How to terminate a user process in SQLServer

I. Scenario: in many cases, the database administrator is often required to terminate the user PROCESS IN SQLServer. This article describes how to create a simple stored procedure to terminate multiple sessions at the same time, end consecutive sessions, and end all sessions connected to the database. In many cases, the database administrator is often required to terminate SQLServ.

I. Scenario: in many cases, the database administrator is often required to terminate the user process in SQL Server. This article describes how to create a simple stored procedure to terminate multiple sessions at the same time, end consecutive sessions, and end all sessions connected to the database. In many cases, the database administrator is often required to terminate the SQL Serv

I. Scenario: in many cases, the database administrator is often required to terminate the user process in SQL Server. This article describes how to create a simple stored procedure to terminate multiple sessions at the same time, end consecutive sessions, and end all sessions connected to the database.

In many cases, the database administrator is often required to terminate the user process in SQL Server, for example, before a database is stopped or restored, or long running of active transactions. The Database Administrator usually uses the "KILL" Command provided by SQL Server to complete the task.

However, the "KILL" Command provided by SQL Server is not flexible enough to end multiple sessions at a time. Only one session can be solved at a time. This article describes how to create a simple stored procedure to terminate multiple sessions at the same time, end consecutive sessions, and end all sessions connected to the database.

First, we create the "KILL2" Process in the master database. The Code is as follows (reference ):

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 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 100, 56, 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 'Kill 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 'illing' + 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 'illing' + 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 'illing' + 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 'illing' + 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 'illing' + 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