Killing SQL Server Processes (SPIDs)

In SQL Server, processes (SPIDs) can sometimes become blocked, leading to performance issues. As a Database Administrator, you may need to terminate such processes to maintain system efficiency. This guide demonstrates how to identify and kill blocking SPIDs.

Contents:
> Creating a Blocking SPID
> Killing a Blocking SPID
> Considerations When Killing SPIDs

How to Kill SQL User Connections

Creating a Blocking SPID

To replicate a blocking scenario, we create a table and initiate a transaction that locks it. Then, we execute a SELECT query, which gets blocked due to the transaction lock.

-- Create a sample table
CREATE TABLE TestTable (ID INT PRIMARY KEY, Name NVARCHAR(50));

-- Start a transaction that locks the table
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Name) VALUES (1, 'Blocked Row');

-- Run a SELECT query in another session to observe the blocking
SELECT * FROM TestTable;

Using sp_who2, sp_whoisactive, or sys.dm_exec_requests, we can identify the blocking SPID.

From the screenshot, you can see that session_id 55 is the blocking SPID, causing the other session to wait for the lock to be released. This happens when a transaction remains open or a query holds onto resources for too long, creating a bottleneck.

To resolve this, we need to terminate the blocking session. This requires identifying the correct SPID (in this case, 55) and ensuring it’s safe to kill, but first try make sure there are no dependencies or unintentional side effects.

Identifying and Killing the Blocking SPID

Once identified, you can terminate the blocking session using the KILL command:

-- Kill the blocking session by replacing <SPID> with the correct value  
KILL <SPID>

Considerations When Killing SPIDs

> Killing a session with an open transaction may trigger a rollback, which could take time. Rollbacks may continue to block things up and can last longer than you’d ever expect. Listen to this story of a 2 month rollback, crazy to even imagine
> Use KILL <SPID> WITH STATUSONLY; to monitor rollback progress. This won’t always return a valid output.
> If sp_who2 or sp_whoisactive doesn’t show the blocking SPID, check the SSMS Activity Monitor.
> Killing SPIDs can help when dropping or detaching databases with active connections on test instances.

For more information on this, check out my latest post on Killing SQL Server SPIDs. This is now, old, but still useful.