How To Kill All SPID In Database or Multiple Databases In Sql Server
1. How To Kill SPID For Specific Database
/*******************************************/
DECLARE
@kill varchar(8000)
Set @kill
= ''
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
/**************************************/
