In SQL Server, Session Process IDs (SPIDs) play a crucial role in identifying and managing SQL connections. The KILL (SPID) command serves as a tool for terminating troublesome sessions. This blog post provides an overview of the KILL command and its practical applications.

The following is included in this one:
> Reviewing SQL SPIDs to Kill
> Killing SQL Server Processes (SPIDs)
> Killing All SPIDs Connected to a Database
> Added Notes for Killing SQL Sessions

Exercise caution when using KILL SPID – It should not be a frequent task for DBA’s to be killing SPIDs. We need to resolve the problem query, for example by optimizing the SQL query code, or adding an index to help speed things along.

You might need to kill a SPID for various reasons. It could be due to a long-running query that is starting to consume too much disk space, or a blocking query that is breaking an application. Identifying the problem query and being confident about it is the harder part.

First in this article we will be reviewing currently running SPIDs on a SQL Server, and then showing some examples of killing SPIDs.

Reviewing SQL SPIDs to Kill

There are many ways to show running queries with SPID info in SQL Server. This includes querying system DMVs, checking SSMS Activity Monitor, SP_Who2 and more. Check out my other blog post for more information on ways to check active SQL queries.

Below is a simple script that queries SQL DMVs to show current SQL sessions:

-- Show current sessions
SELECT 
     s.host_name, 
     s.program_name, 
     r.blocking_session_id 'Blk by', 
     Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid,st.dbid)), '') AS command_text, 
     s.session_id, 
     r.cpu_time, 
     r.logical_reads, 
     r.reads, 
     r.status, 
     r.wait_type, 
     wait_resource, 
     r.wait_time / (1000.0) 'Wait (in sec)', 
     r.writes, 
     r.total_elapsed_time / (1000.0) 'Elapsed (in sec)', 
     SUBSTRING(st.TEXT,(r.statement_start_offset / 2) + 1, 
     ((CASE r.statement_end_offset 
          WHEN -1 
          THEN Datalength(st.TEXT) 
          ELSE r.statement_end_offset 
          END - r.statement_start_offset) / 2) + 1) AS statement_text, 
     r.command, 
     s.login_name, 
     s.last_request_end_time, 
     s.login_time, 
     r.open_transaction_count 
FROM  sys.dm_exec_sessions AS s 
JOIN sys.dm_exec_requests AS r 
ON r.session_id = s.session_id 
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
ORDER BY r.cpu_time DESC;

When we review running SPIDs, we should understand, typically SPIDs 1 to 50 are designated for SQL Server’s internal processes, with user processes beginning from SPID 51 onwards. As additional tasks are undertaken within your environment, you may observe more than just 1-50 being used by the SQL internal systems. An error message should prevent you from killing core SQL system SPIDs anyway.

Usually we’re looking for blocking or long-running queries, more commonly those are the problem sessions.

Note down the SPID number, and move onto the next step when we’re confident we want that session to end.

Killing a SQL Server Process (SPID)

To kill a SQL SPID, insert the SPID number after the word KILL, then run the command:

-- kill a sql session (SPID)
KILL <SPID>

When the kill command has been executed, the session should end, or try to and start rolling back.

We can monitor the progress of a terminated SPID rollback by running “KILL WITH STATUSONLY“. This SQL command generates a progress report for rolling back session IDs or Unit of Work (UOW) after a “KILL session ID|UOW” statement has been run. The output report includes rollback completion percentage and estimated time remaining, however it does not always return a value.

When a rollback is happening and it’s causing issues with your application or system, you might have to just wait it out. However, that might not be an easy option…. I’ll never forget hearing about this 2 month rollback of a killed SPID! Be prepared for an alternative recovery plan if things are really that bad.

Killing All SPIDs on a Database

DBA’s often encounter scenarios where they need to terminate all user sessions/processes for a specific database. This may be necessary during maintenance tasks, e.g. building/migrating a new SQL Server, or perhaps for when trying to drop a database.

I’ve set this script to print results instead of execute for safety. You can get it going by uncommenting the EXEC command line.

--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('DATABASE123') -- Change Database Name here
PRINT @kill
-- EXEC(@kill) --uncomment when ready

Added Notes for Killing SQL Sessions

The KILL SPID command can be used to resolve blocking scenarios by terminating the offending session, restoring system functionality. Or, you may be facing a runaway SPID that has been executing a very long time without yielding any progress for an extended period. There’s various reasons why you might need to kill a SQL session, however it should not be something you need to do often as I’ve described in this post.

It is important to note that killing a SPID can cause rollbacks, which can take longer than expected and continue to block other queries. You can use the KILL command with the STATUSONLY option, or query system DMVs to check the status of a killed query. In some cases, a lead blocking SPID may not appear in sp_whoisactive results, in which case you can use Activity Monitor to find the correct SPID to kill.

Killing all sessions on a database might be necessary during maintenance tasks, for example dropping a non-Production database.

Hope this blog post was a useful one for you! Feel free to check out my SQL Server Processes tag blog for some more related info.