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.