Terminate User processes in SQL Server

September 3, 2008

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers