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:
--Create a test database. CREATE DATABASE myDatabase --Change database context. USE myDatabase GO --Create a test table. DROP TABLE IF EXISTS myTable CREATE TABLE myTable ( firstname VARCHAR(25), surname VARCHAR(25), email VARCHAR(50) ) --Start explicit transaction but do not commit. BEGIN TRANSACTION INSERT INTO myTable VALUES ( 'pete', 'whyte', 'email@example.com' ) --COMMIT /* --Now run a select statement in a new query window on the table we have just created. --The insert has a lock on the table until committed, so it'll continue to execute. --Open another query window (or you can come back to this one) and run sp_whoisactive. */ USE [DBTools] GO sp_whoisactive
The above should result in something looking like this.
Now onto the killing!
When you have identified the correct SPID to kill, the SQL is simple:
--Kill a SPID --It's always a risk to kill anything -- Rollbacks may be painful. KILL 60; GO --KILL WITH STATUSONLY does not KILL -- Shows the current progress of the rollback. KILL 60 WITH STATUSONLY; GO
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:
--Kill all user SPIDs USE [master] GO DECLARE @kill VARCHAR(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('funky_db') PRINT @kill --EXEC(@kill) --uncomment when ready