Technical Article

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.

DECLARE @dbspid as int
DECLARE @dbname as varchar(50)
DECLARE @killcommand as varchar(50)
SET @dbname = 'db1,db2'
--SET @dbname = 'db1'
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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating