Sometimes in SQL Server, you encounter processes that need to be terminated—whether it’s a query running wild, consuming too many resources, or holding up others.
In this demo, we’ll set up a blocking SPID to simulate such a scenario, then resolve it by identifying and killing the offending process.
> Creating a Blocking SPID
> Killing a Blocking SPID
Demo Setup of Killing a Blocking SQL Session
Creating a Blocking SPID
To replicate a blocking scenario, I set up a table and initiated a query that holds a transaction lock. Following that, I executed a SELECT statement on the same table, which gets blocked by the locking SPID (session_id 55).
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—make sure there are no dependencies or unintentional side effects.
For more info on managing blocking scenarios and understanding transaction behavior, you can check out this post on isolation levels by Kendra Little.
Now, let’s move forward with resolving the block!
Resolving the Block: Killing the SPID
Now that we’ve identified the blocking SPID, it’s time to resolve issues by killing the session.
Simply use the KILL command as shown below with the SPID you’ve determined is causing the problem:
-- Kill the blocking session by replacing <SPID> with the correct value
KILL <SPID>And that’s it! The blocked query can now proceed, and your SQL Server environment is back to normal.
Be aware as mentioned in the code, 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.
WITH STATUSONLY won’t always return results. I’ve also had an occasion where the lead block SPID didn’t show up when running sp_whoisactive. In this case I had to look for the process to kill via Activity Monitor then all was good again.
Killing SPIDs can be also useful if you’re having trouble dropping or detaching a database. This can happen if there’s still activity going on such as remote users or an application using connection pooling. Check out my latest post on Killing SPIDs for more info, including killing all SPIDs.
Leave a Reply