Kill all user SPIDs in a SQL Server Database

Using this script you can kill all the SPID connected to TEST2008 database 

 

 

 

DECLARE @DB SYSNAME 

SET @DB = 'Test2008'  -- Here you need to give the database name where you need to kill all connection

 

 

DECLARE @SPID VARCHAR(4), @cmdSQL VARCHAR(10)

 

IF OBJECT_ID('tempdb..#KilledSpidsInfo ') IS NOT NULL

BEGIN

DROP TABLE  #KilledSpidsInfo

END

 

SELECT * into #KilledSpidsInfo FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

select * from #KilledSpidsInfo

 

DECLARE cCursor CURSOR

FOR SELECT CAST(SPID AS VARCHAR(4)) FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

OPEN cCursor

 

FETCH NEXT FROM cCursor INTO @SPID

 

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmdSQL = 'KILL ' + @SPID

 

 EXEC (@cmdSQL)

FETCH NEXT FROM cCursor INTO @SPID

END

 

CLOSE cCURSOR

 

DEALLOCATE cCURSOR