How to Kill User Sessions (SPID) in SQL Server? – Interview Question of the Week #141

How to kill a user session in SQL Server? The command is short. My first question in an interview is why someone wants to run it.

I’ve heard “kill all the sessions” as a response to a busy server. If those connections are doing the work we asked them to do, ending them all replaces a performance problem with interrupted users and transaction rollbacks. I’d identify the particular session and its activity first.

Find the session

This read-only query lists user sessions, excludes the connection running it, and shows any current request. The proposed command is text for review. The query does not end a connection.

SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       r.command,
       r.blocking_session_id,
       CONCAT('KILL ', s.session_id, ';') AS ProposedCommand
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
  ON r.session_id = s.session_id
WHERE s.is_user_process = 1
  AND s.session_id <> @@SPID
ORDER BY s.session_id;

Check the login, application, host, request, and transaction context. Then decide whether ending one connection is justified. Session IDs can be reused after a connection ends, so identify the session again immediately before acting.

End one reviewed connection

For a chosen session, the command is KILL followed by its numeric session ID. For example, if you have just verified session 57 in a lab:

-- Example only. Verify the current session ID before running.
KILL 57;

SQL Server rolls back an open transaction belonging to that connection. A large rollback can take time. During a rollback, KILL 57 WITH STATUSONLY reports progress without issuing a second termination request. You also need permission to alter a connection.

My original script concatenated a KILL command for every session with an ID above 50 and executed the whole string. I wouldn’t hand that script to a candidate or run it on a busy instance. The numeric cutoff doesn’t describe business intent, and it doesn’t tell us which connection caused the incident. If the actual goal is exclusive access to one database, the single-user database procedure addresses a different, database-scoped task.

The better interview answer starts with the reason, shows the sessions, and ends only the connection that needs intervention. I’d hire that reasoning before I’d hire a memorized mass-kill script.

References: Microsoft KILL documentation and sys.dm_exec_sessions.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.