Menu & Search
Killing SQL Server Processes (SPIDs)

Killing SQL Server Processes (SPIDs)


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', 'myemail@address.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.

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:

  --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

Share

2 Comments

  1. […] 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. […]

  2. […] my other post for more on killing […]