DBAtlas · Database operations atlas

Kill a session (SPID)

SQL ServerSession

Command

-- First find the offending SPID:
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       r.status, r.command, r.wait_type, r.blocking_session_id,
       DB_NAME(r.database_id) AS db
FROM   sys.dm_exec_sessions s
  LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
WHERE  s.is_user_process = 1;

-- Kill (example SPID 63):
KILL 63;

-- Show rollback progress:
KILL 63 WITH STATUSONLY;

What it does

Terminates the specified session_id (SPID); its open transaction is rolled back.

When to use it

To stop a session that holds locks, runs a runaway/long query, or does not release its connection.

Caution

On a large open transaction the rollback can take a long time and cannot be canceled; monitor progress with KILL WITH STATUSONLY. Do not kill system SPIDs (is_user_process=0). Confirm the correct SPID.

Open in DBAtlas →

Related