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
/**************************************/


No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services