Skip to main content

This script opens a little cursor that will clean out connections to any DB's you specify. It will release SPID's that are active on a DB or which are locking a related SPID. I found the base script some time ago -I wish I remembered the author or location- but I've modified it ever so slightly to provide for a good package that can be used prior to starting a DB restore for example.
Feel free to play with it and adapt it to whatever needs you may have. I've used this, at times, to kill SPID's which were causing blocking conditions. You should be able to easily modify it to go after very specific conditions.

--
-- DB Connection Killer
--
-- This script opens a little cursor that will clean out connections to any DBs
-- you specify. It will release SPIDs that are active on a DB or which are
-- locking a related SPID. I found the base script some time ago -I wish I
-- remembered the author or location- but I've modified it ever so slightly to
-- provide for a good package that can be used prior to starting a DB restore
-- for example.
--
-- Feel free to play with it and adapt it to whatever needs you may have. I've
-- used this, at times, to kill SPIDs which were causing blocking conditions.
-- You should be able to easily modify it to go after very specific conditions.
-- 
-- http://www.sqlservercentral.com/scripts/cursors/63784/

declare @dbspid as int;
declare @dbname as varchar(50);
declare @killcommand as varchar(50);

set @dbname = 'db1,db2'; -- set database names

declare ConnectionKiller cursor
    for
        select sysproc.spid
        from sysprocesses as sysproc
        inner join sysdatabases as sysdb
            on sysdb.dbid = sysproc.dbid
        where sysdb.NAME in(@dbname)
            and sysproc.spid <> 0
        union
        select sysproc.blocked
        from sysprocesses as sysproc
        inner join sysdatabases as sysdb
            on sysdb.dbid = sysproc.dbid
        where sysdb.NAME in(@dbname)
            and sysproc.blocked <> 0;

open ConnectionKiller;

fetch next from ConnectionKiller into @dbspid;
while @@FETCH_STATUS = 0
    begin
        set @killcommand = 'KILL ' + cast(@dbspid as varchar(50));
        exec (@killcommand);
        fetch next from ConnectionKiller into @dbspid;
    end;
close ConnectionKiller;
deallocate ConnectionKiller;