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.
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.
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' + @db_name + '" database' 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 = '' 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.