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'  go

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; 

Running the command;

 

This gives the information only. This is not terminating the SPID 58. Still it is in active state. Let’s see.

 

Still Session ID 58 is active.