Have you ever tried to restore over a database but found that all attempts are being blocked by an annoying SPID? Or had hundreds of orphaned SPIDs running crazy on your instance?
Well I have the solution for you with the below script which uses a cursor to kill all connections to a database.
DECLARE @spid varchar(10)</pre> DECLARE kill_spid CURSOR fast_forward FOR SELECT SPID FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'AdventureWorks2012' AND spid > 50 OPEN kill_spid FETCH NEXT FROM kill_spid INTO @spid while @@FETCH_STATUS = 0 BEGIN EXEC ('Kill ' + @Spid) FETCH NEXT FROM kill_spid INTO @spid END CLOSE kill_spid DEALLOCATE kill_spid
Make sure that you change AdventureWorks2012 for your database name and double check that you have the correct database name as I have seen it when people put the wrong database name in and its never a pretty sight.