Terminate the user process method in SQL Server

Scenario: In many cases, the database administrator is often asked to terminate the user process in SQL Server. This article will show you how to create a simple stored procedure to implement features such as terminating multiple sessions at the same time, ending consecutive sessions, and ending all sessions connected to the database.

In many cases, the database administrator is often asked to terminate user processes in SQL Server, such as when a database is stopped, or before a database is restored, or when an active transaction is run for a long time. The database administrator typically uses the KILL command provided in SQL Server to complete the task.

However, the "KILL" command provided by SQL Server is not flexible enough to end multiple sessions at once and only one session can be resolved at a time. This article will show you how to create a simple stored procedure to implement features such as terminating multiple sessions at the same time, ending consecutive sessions, and ending all sessions connected to the database.

First, we create the "KILL2" process in the main database, as shown in the code below (refer to figure I):

Use [master]
Go
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 sessions IDs from 57
--usage2:kill2 '--> ' 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 sessions IDs that are connected
To the database "MyDatabase"

Use master
Go
set concat_null_yields_null out
Go
CREATE PROCEDURE kill2 @param2 varchar (+)
AS
--declare @param2 varchar
DECLARE @param varchar ()
DECLARE @startcount int
declare @killcmd varchar (
Declare @endcount int
Declare @spid int
Declare @spid2 int
declare @tempvar varchar (MB)
Declare @tempvar2 varchar
--set @param2 = ' The '
Set @param =replace (@param2, ', ')
If CHARINDEX ('-', @param) <& Gt 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), @startcount) + "to" +convert (varchar), @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, @startcount)
Set @killcmd = ' Kill ' +convert (varchar), @startcount)
Exec (@ Killcmd)
End
Else
begin
Print ' cannot kill the SPID ' +convert (varchar, @startcount) + ' because it do Es 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), @tempvar2) and spid>50)
If @spid = CONVERT (varchar (100) , @tempvar2)
begin
print ' killing ' +convert (varchar), @tempvar2)
Set @killcmd = ' Kill ' +convert (varchar ( @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 (', ', @tempv AR),
end
Set @spid = (select spid from master.dbo.sysprocesses where spid=convert (varchar), @tempvar) and SPID>50)
If @spid = CONVERT (varchar, @tempvar)
begin
print ' killing ' +convert (varchar), @tempvar
Set @killcmd = ' Kill ' +convert (varchar, @tempvar)
ExEC (@killcmd)

End
Else
begin
Print ' cannot kill the SPID ' +convert (varchar), @tempvar) + ' Becaus E It does not Exist '
End
End

If CHARINDEX (' = ', @param2) <>0
Begin
print ' Killing all of 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), @spid2)
Set @killcmd = ' Kill ' +convert (varchar), @spid2)
EXEC (@killcmd)

End
Else
Begin
Print ' cannot kill the SPID ' +convert (varchar, @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), @param) and spid>50)
If @spid = CONVERT (varchar), @param)
Begin
print ' Killing ' +convert (varchar), @param)
Set @killcmd = ' Kill ' +convert (varchar), @param)
EXEC (@killcmd)

End
Else
Begin
Print ' cannot kill the SPID ' +convert (varchar, @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