Kill All Connections to a SQL Server database

Useful script to kill all connections for a given database. Use this if you are getting deadlocks or you have processes that are running too long. Use with caution.

Hope that helps, Jon Bosker, DB Gurus, 17 Nov 2015


USE master
GO


SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'MyDatabase' -- <<<<< -----change this------------
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
PRINT @spidstr
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END