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: