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.
Originally published at https://parvtheitgeek.com on March 27, 2015.
