Cool Tips‎ > ‎Connectivity‎ > ‎

Connectivity - Kill all DB Connections

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested

Credits:

Author:   ChillyDBA
Date:      2006
 

Description

This is a simple script that I use almost on a weekly basis. 
Normally it gets prefixed on the beginning of a restore command when refreshing DEV/TEST/QA/STAGING databases.
I wouldn't normally recommend its use in a PROD environment, but there have been occasions in the past when it has been necessary.   

Code

 SET NOCOUNT ON

DECLARE
  
@cmd VARCHAR(100),
    
@spid INT,
    
@dbname SYSNAME

SELECT
@dbname = '<Database Name>'

SELECT @spid = MIN(spid)
FROM MASTER..sysprocesses
WHERE DB_NAME(dbid) = @dbname

WHILE @spid IS NOT NULL
BEGIN
   SELECT
@cmd = 'kill ' + CAST(@spid AS VARCHAR(10))

  
EXEC(@cmd)

  
SELECT CAST(@spid AS VARCHAR(10)) + ' killed'

  
SELECT @spid = MIN(spid)
  
FROM MASTER..sysprocesses
  
WHERE DB_NAME(dbid) = @dbname
  
AND spid > @spid
END

SET NOCOUNT OFF  

 
ċ
Kill ALL connections for a specified database.sql
(0k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments