Microsoft Business Intelligence

Saturday 16 September 2023

Kill command in SQL server

When we are connection to the SQL server instance or opening the new query window, SQL server assigns a unique identification number to each connection. It is the Server Process ID and commonly known as SPID.

With the help of @@SPID we will get the current SPID. See below.

SPID 1 to 50 is reserved for SQL Server internal processes, and the user process starts from SPID 51.

We can get the all active SPID using SP_WHO or SP_WHO 2 command.

Kill Command

This command is use to Ends a user process that is based on the session ID.

Syntax

Kill <SPID>

This kill the particular session.

Example

Here both Session ID 56 and 66 are connected. Now we are killing the session id 66 for session ID 56 window. Running the kill command.

SPID : 66 killed successfully.

If we run again we will get the below message

Below is the script to kill the all SPID for a database

DECLARE @str_sql NVARCHAR(1000),

@db_name VARCHAR(100),

@number_of_session_killed INT

SET @db_name = 'AdventureWorks2019'

PRINT 'START: Killing active connections to the "'

+ @db_name + '" database'

-- Count Connections

SELECT @number_of_session_killed = Count(*)

FROM master.dbo.sysprocesses

WHERE Db_name(dbid) = @db_name

AND dbid <> 0

AND spid <> @@spid

-- Create the sql to kill the active database connections

SET @str_sql = ''

SELECT @str_sql = @str_sql + 'kill ' + CONVERT(CHAR(10), spid) + ' '

FROM master.dbo.sysprocesses

WHERE Db_name(dbid) = @db_name

AND dbid <> 0

AND spid <> @@spid

EXEC (@str_sql)

PRINT @str_sql

PRINT 'END: Killed "'

+ Cast(@number_of_session_killed AS VARCHAR(4))

+ '" active connections to the "' + @db_name

+ '" database'

Running this script

We can't kill our own process also shouldn't kill the following processes:

Ø AWAITING COMMAND
Ø CHECKPOINT SLEEP
Ø LAZY WRITER
Ø LOCK MONITOR
Ø SIGNAL HANDLER

KILL WITH STATUSONLY

This option tells SQL Server to terminate the specified process and provide information about the termination status without actually terminating the process. It's a way to check the status of the process before deciding whether to proceed with termination.

Syntax

KILL SPID WITH STATUSONLY;