There are many reasons why you’d KILL a SPID in SQL Server. Whether it’s impending disk space doom due to data or log file bloat, or a long running query that’s lost in space… When alerted to this situation I’d run sp_whoisactive in the first instance. When the picture is clear, the kill happens.
To test this, first create a blocking SPID by running the following:
The above should result in something looking like this.
As highlighted, you can see that my select statement is being blocked by the uncommitted insert. For a bit of further reading, have a look at Isolation Levels by Kendra Little.
Now onto the killing!
When you have identified the correct SPID to kill, the SQL is simple:
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. The following script will kill all SPIDs on a specified database:
[…] Running sp_whoisactive now will look something like this. As you can see I’ve highlighted the blocking sessions. For more information on encountering such events, see my blog post – Killing SQL Server Processes. […]
[…] my other post for more on killing […]