Kill all DB Connections

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

Author: ChillyDBA

Date: 2006

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.

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