How To Kill All SPID In Database or Multiple Databases In Sql Server

If you want to kill spid for any specific database or more than one database you can use below option. If some one is suggesting you to use database Offline/ Online  or Single user mode , Please avoid WHY. 


1. How To Kill SPID For Specific Database

/*******************************************/
DECLARE @kill varchar(8000) = ''
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('database_name') and spid > 50 and spid <> @@spid
EXEC(@kill);
/*******************************************/

2. Procedure To Kill All SPID For Specific Database

/*******************************************/
Create procedure KILL_SPID (@db varchar(8000) )
as 

----Created By: Saurabh Sinha

----Date: 26/Nov/2014

----Description: This sp can take 1 database as parameter and kill all spids on db

----SYNTAX: KILL_SPID ('db1')

Begin
DECLARE @kill varchar(8000) = ''

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id(rtrim(ltrim(@db))) and spid > 50 and spid <> @@spid
Select @kill
EXEC(@kill);
Select 'All spid mention above are killed'
END

/*******************************************/

3. Procedure To Kill All SPID For Multiple Database

/**************************************/

Create procedure KILL_SPID_ON_MULTIPLE_DB (@db varchar (max))

as 

----Created By: Saurabh Sinha

----Date: 26/Nov/2014

----Description: This sp can take any no. of databases as parameter seperated with comma and kill all 

----spids on MULTIPLE DB

----SYNTAX: KILL_SPID_ON_MULTIPLE_DB ('db1,db2,db3 , db4,db5')

Begin

--Declare  @db  varchar(8000) 

Declare @db1 varchar(8000)

Declare @count int , @count1 int , @len int

DECLARE @kill varchar(8000) = ''

select @count = len(@db) - len(replace(@db,',',''))

while @count > -1

begin

Set @db1 = LEFT(@db, CHARINDEX(',',@db+',')-1)

Set @db = STUFF(@db, 1, CHARINDEX(',',@db+','), '')

Set @count =  @count-1

set @db1=  rtrim(ltrim(@db1))

select @db1

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'

FROM master..sysprocesses 

WHERE dbid = db_id(@db1) and spid > 50 and spid <> @@spid

select @kill

EXEC(@kill);

End

END

/**************************************/

4. How To Kill All Spid For Multiple Databases

/**************************************/

Declare  @db  varchar(8000) ,@db1 varchar(8000)

Declare @count int , @count1 int , @len int

DECLARE @kill varchar(8000) = ''

set @db = 'test1,test2, test3'

select @count = len(@db) - len(replace(@db,',',''))

while @count > -1

begin

Set @db1 = LEFT(@db, CHARINDEX(',',@db+',')-1)

Set @db = STUFF(@db, 1, CHARINDEX(',',@db+','), '')

Set @count =  @count-1

set @db1=  rtrim(ltrim(@db1))

select @db1

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'

FROM master..sysprocesses 

WHERE dbid = db_id(@db1) and spid > 50 and spid <> @@spid

select @kill

EXEC(@kill);

End

/**************************************/