In SQL Server, Session Process IDs (SPIDs) play a crucial role in identifying and managing SQL connections. The KILL SPID command serves as a tool for terminating troublesome sessions. This article provides an overview of the KILL command and its practical applications.
Exercise caution when using KILL SPID. It should not be a frequent task for any DBA to be killing SPIDs. We need to resolve the problem query, for example by optimizing the SQL or with an index.
How to Kill SPIDs in SQL Server
1. Reviewing SPIDs to Kill
There are many ways to show running SPIDs in SQL Server. This includes System DMVs, Activity Monitor, SP_Who2 and more. Below is a simple script that queries SQL DMVs to show current SQL sessions:
-- Show SQL Sessions
SELECT conn.session_id, host_name, program_name,
nt_domain, login_name, connect_time, last_request_end_time
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_exec_connections AS conn
ON sess.session_id = conn.session_id;
When we review running SPIDs, we should understand, usually SPIDs 1 to 50 are designated for SQL Server’s internal processes, with user processes beginning from SPID 51 onwards. As additional tasks are undertaken within your environment, you may observe more entries in the Task Manager. An error message should prevent you from killing core SQL system SPIDs.
Also, you are unable to kill your own current SPID in MS SQL. To view your own SPID it’s typically displayed at the top of the SSMS query window, next to the pin icon. Alternatively, you can use the @@SPID global variable to view it programmatically.
Another thing of note as per MS Docs is, you should not kill the following SPIDs in SQL Server:
– AWAITING COMMAND
– CHECKPOINT SLEEP
– LAZY WRITER
– LOCK MONITOR
– SIGNAL HANDLER
SQL Query to Show Blocking: For a better query to show current sessions including blocking in SQL Server, check out my other post, Quick Performance Troubleshooting Script for SQL Server.
2. Killing SPIDs
The KILL SPID command can be used to resolve blocking scenarios by terminating the offending session, restoring system functionality. Or, you may be facing a runaway SPID that has been executing a long-running query without yielding any progress for an extended period. There’s various reasons why you might need to kill a SQL session, however it should not be something you need to do often.
Once you have the SPID you want to kill identified, we can terminate the session using the KILL <SPID> command:
-- Kill SQL SPID kill 69;
When the kill command is executed, the session should end, or try to and start rolling back.
For more special case requirements, check out my other post for killing all SPIDs on a database. Killing all sessions on a database may be necessary during maintenance tasks, troubleshooting (dropping a database), or other critical database operations.
3. Monitoring Rollback Progress
We can monitor the progress of a terminated SPID rollback by running “KILL WITH STATUSONLY“. This SQL command generates a progress report for rolling back session IDs or Unit of Work (UOW) after a “KILL session ID|UOW” statement has been run. The output report includes rollback completion percentage and estimated time remaining, however it does not always return a good value.
When a rollback is happening, if it’s causing issues with your application or system, you might have to just wait it out. I’ve heard stories of rollbacks taking more than a month to complete! Be prepared incase this rollback takes a very long time.
Leave a Reply