SQL Cursor to Kill all connections to a database

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)
FROM kill_spid INTO @spid
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.


Leave a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: