Terminate User processes in SQL Server

Database administrators are often required to terminate the user process in situations such as de-comissioning a database, before restoring a database or long running open transactions, etc. In all of these cases they would use the “KILL” command provided in SQL Server.


The “KILL” command provided by SQL Server is not flexible enough to kill many sessions at the same time. It can only be used for one session at a time. This article illustrates how to create a simple procedure to kill many sessions at the same time, kill a range of sessions and kill all of the sessions connecting to a database.


Let us create the procedure “KILL2” in the master database as shown below. [Refer Figure 1.0]

USE [master]
GO
/****** Object: StoredProcedure [dbo].[kill2]
Script Date: 08/27/2008 16:21:40 ******/
IF EXISTS (SELECT * FROM master.dbo.sysobjects
WHERE id = OBJECT_ID(N’[kill2]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[kill2]
GO
–Usage1: Kill2 ’51-57′ –> Kills all the session IDs from 51 to 57
–Usage2: Kill2 ’58’ –> Kills the session IDs 58
–Usage3: Kill2 ‘51,56,100,58’
–> Kills the session IDs 51,56,100 and 58
–Usage4: Kill2 ‘DB=MyDatabase’
–> Kills all the session IDs that are connected
to the database “MyDatabase”

use master
go
set concat_null_yields_null off
go
create procedure kill2 @param2 varchar(500)
as
–declare @param2 varchar(500)
declare @param varchar(500)
declare @startcount int
declare @killcmd varchar(100)
declare @endcount int
declare @spid int
declare @spid2 int
declare @tempvar varchar(100)
declare @tempvar2 varchar(100)
–set @param2 =’54’
set @param=REPLACE(@param2,’ ‘,”)
if CHARINDEX(‘-‘,@param) <> 0
begin
select @startcount= convert(int,SUBSTRING(@param,1,charindex(‘-‘,@param)-1))
select @endcount=convert(int,SUBSTRING(@param,charindex(‘-‘,@param)+1,(LEN(@param)-charindex(‘-‘,@param))))
print ‘Killing all SPIDs from ‘ + convert(varchar(100),@startcount)+’ to ‘ +convert(varchar(100),@endcount)
while @startcount <=@endcount
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=@startcount and spid>50)
if @spid = @startcount
begin
print ‘Killing ‘+convert(varchar(100),@startcount)
set @killcmd =’Kill ‘+convert(varchar(100),@startcount)
exec(@killcmd)
end
else
begin
Print ‘Cannot kill the SPID ‘ +convert(varchar(100),@startcount) + ‘ because it does not Exist’
end
set @startcount=@startcount + 1
end

end

if CHARINDEX(‘,’,@param) <> 0
begin
set @tempvar =@param
while charindex(‘,’,@tempvar ) <> 0
begin
SET @tempvar2=left(@tempvar,charindex(‘,’,@tempvar)-1)
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar2) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar2)
begin
print ‘Killing ‘+CONVERT(varchar(100),@tempvar2)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@tempvar2)
exec (@killcmd)

end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@tempvar2) + ‘ because it does not Exist’
end
set @tempvar =REPLACE(@tempvar,left(@tempvar,charindex(‘,’,@tempvar)),”)
end
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar)
begin
print ‘Killing ‘+CONVERT(varchar(100),@tempvar)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@tempvar)
exec (@killcmd)

end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@tempvar) + ‘ because it does not Exist’
end
end

if CHARINDEX(‘=’,@param2) <>0
begin
print ‘Killing all the SPIDs that are connected to the database ‘+RIGHT(@param2,(len(@param2)-3))
declare dbcursor
cursor forward_only for select SPID from master.dbo.sysprocesses where DB_NAME(dbid) = RIGHT(@param2,(len(@param2)-3))
open dbcursor
fetch dbcursor into @spid
while @@FETCH_STATUS =0
begin
set @spid2=(select spid from master.dbo.sysprocesses where spid=@spid and spid>50)
if @spid = @spid2 begin
print ‘Killing ‘+CONVERT(varchar(100),@spid2)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@spid2)
exec (@killcmd)

end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@spid2) + ‘ because it does not Exist’
end

fetch dbcursor into @spid
end
close dbcursor
deallocate dbcursor

end

if CHARINDEX(‘-‘,@param)=0 and CHARINDEX(‘,’,@param) = 0 and CHARINDEX(‘=’,@param)=0
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@param) and spid>50)
if @spid = CONVERT(varchar(100),@param)
begin
print ‘Killing ‘+CONVERT(varchar(100),@param)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@param)
exec (@killcmd)

end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@param) + ‘ because it does not Exist’
end

end
go
–kill2 ’51’
–go
–kill2 ’51-56′
–go
–kill2 ‘56,57,58,52’
–go
–kill2 ‘db=AdventureWorks2008’
–kill2 ‘db=My Database’
–go
–sp_who


Download the code from here



create procedure
Figure 1.0


Usage 1


Now let assume that the following SPIDs 51, 52, 53, 54, 55, 57 [Refer Figure 1.1] are connected to the SQL Server and you want to kill only the SPIDs 54, 57 and 55.



kill certain SPIDs
Figure 1.1


Execute the following command as shown below.


Note: In this example, I am also trying to kill some other SPIDs that do not exist in SQL Server.

use master
go
kill2 ‘54,57,55,61,100’
go

Result

Killing 54
Killing 57
Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Cannot kill the SPID 55 because it does not Exist
Cannot kill the SPID 61 because it does not Exist
Cannot kill the SPID 100 because it does not Exist


cannot kill SPID
Figure 1.2


From the result [Refer Figure 1.2], you can see that it killed the SPID 54. When it attempted killing 57 it failed. And it also displayed the information on why it could not kill certain SPIDs.


Usage 2


Now let’s assume that we have the following SPIDs 51, 52, 53, 54, 55, 57, 58, 59 and 60 and we want to kill all of the SPIDs ranging from 25 to 70.


Execute the command shown below.

use master
go
kill2 ’25-75′
go

Result

Killing all SPIDs from 25 to 75
Cannot kill the SPID 25 because it does not Exist
…..
Cannot kill the SPID 48 because it does not Exist
Cannot kill the SPID 49 because it does not Exist
Cannot kill the SPID 50 because it does not Exist
Killing 51
Killing 52
Killing 53
Killing 54
Killing 55
Cannot kill the SPID 56 because it does not Exist
Killing 57
Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Killing 58
Killing 59
Killing 60
Cannot kill the SPID 61 because it does not Exist
…..
Cannot kill the SPID 75 because it does not Exist


kill a range of SPIDs
Figure 1.3


From the result you see the “KILL2” procedure ignored all of the connections below SPID 50 and it started killing all of the SPIDs from 51 to SPID 70.


Usage 3


Let’s assume that you want to kill all of the sessions that are connected to the database AdventureWorks2008. Let us assume the following SPIDs 53, 54,58 and 60 are connected to the the database AdventureWorks2008 as shown below. [Refer Figure 1.4]



kill all sessions connected to the database
Figure 1.4


Now let’s kill all of these sessions by executing the following transact SQL statement.

Use master
go
kill2 ‘db=AdventureWorks2008’
go

Result

Killing all the SPIDs that are connected to the database AdventureWorks2008
Killing 53
Killing 54
Killing 58
Killing 60


Kill sessions using transact SQL statement
Figure 1.5


From the result you can see that the “KILL2” procedure killed all of the sessions that were connected to the database AdventureWorks2008.


Usage 4


Type 4 usage of the procedure is similar to the “KILL” command. You can kill one session at a time as shown below.

Use master
go
kill2 ’56’
go

Conclusion


This article has illustrated how to create a simple procedure to kill many sessions at the same time and also kill a range of sessions and kill all the sessions connecting to a database, etc.


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles