SQL Server Kill Sessions: Filtered SPID Management

Kill SQL Server Sessions with Targeted Filters Using KILL and dm_exec_sessions

This article covers a practical T-SQL script that kills one or more SQL Server sessions (SPIDs) while giving you precise control over which sessions are targeted. The script uses sys.dm_exec_sessions to query active sessions and a cursor to issue KILL commands only against sessions that match your chosen filters.

Purpose and Overview

When you need to disconnect users from a SQL Server database — whether before taking it offline, restoring it, or clearing stuck sessions — the built-in KILL command works on one session at a time. Writing a loop to kill many sessions adds complexity. This script wraps that logic into a reusable tool with a safety switch and seven filter parameters so you can scope kills as broadly or narrowly as needed.

The script targets only user processes and automatically excludes the session running it and any SQL Server service accounts. This prevents accidental self-termination and protects service-level logins.

Common use cases include:

• Clearing all connections to a database before a restore or detach
• Killing all sessions for a specific application login
• Removing only sleeping sessions while leaving active queries running
• Cleaning up sessions with open transactions that are blocking other work
• Disconnecting sessions that have been idle for more than a set number of minutes

Code Breakdown

The full script is shown below. The sections that follow explain each part in detail.

 1/*
 2	This is a script that can be used to kill multiple sessions
 3on a SQL Server instance, while also providing the ability to 
 4target sessions based on specific filters.
 5*/
 6USE [master]
 7GO
 8
 9DECLARE  @SPID            SMALLINT
10        ,@ExecSQL         VARCHAR(11)
11        ,@Confirm         BIT
12        ,@ForLogin        NVARCHAR(128)
13        ,@SPIDState       VARCHAR(1)
14        ,@OmitLogin       NVARCHAR(128)
15        ,@ForDatabase     NVARCHAR(128)
16        ,@HasOpenTran     VARCHAR(1)
17        ,@ReqOlderThanMin INT;
18
19SET @Confirm          = 0;   /* Switch to 1 to execute — safety check */
20SET @ForLogin         = N''; /* Only kill SPIDs for this login; empty = all */
21SET @SPIDState        = '';  /* S=sleeping, R=running, empty=all */
22SET @OmitLogin        = N''; /* Kill all except this login; empty=none omitted */
23SET @ForDatabase      = N''; /* Kill SPIDs hitting this database; empty=all */
24SET @HasOpenTran      = '';  /* Y=only sessions with open transactions */
25SET @ReqOlderThanMin  = 0;   /* Kill SPIDs older than N minutes; 0=all */
26
27IF (@Confirm = 0)
28BEGIN
29    PRINT '@Confirm is set to 0. No sessions were killed.'
30    RETURN
31END
32
33DECLARE KillSPIDCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
34  SELECT DISTINCT [session_id]
35  FROM   [master].[sys].[dm_exec_sessions]
36  WHERE  [login_name] = CASE
37                          WHEN @OmitLogin = N'' AND @ForLogin = N'' THEN [login_name]
38                          WHEN @OmitLogin <> N'' AND @ForLogin = N'' THEN
39                            (SELECT DISTINCT [login_name] FROM [master].[sys].[dm_exec_sessions]
40                             WHERE [login_name] <> @OmitLogin)
41                          WHEN @ForLogin <> N'' THEN @ForLogin
42                        END
43    AND [session_id] <> @@SPID
44    AND [is_user_process] = 1
45    AND [database_id] = CASE WHEN @ForDatabase <> N'' THEN DB_ID(@ForDatabase) ELSE [database_id] END
46    AND [login_name] NOT IN (SELECT [service_account] FROM [master].[sys].[dm_server_services] WHERE [status] = 4)
47    AND [status] = CASE WHEN @SPIDState = 'S' THEN N'sleeping' WHEN @SPIDState = 'R' THEN N'running' ELSE [status] END
48    AND [last_request_start_time] <= CASE WHEN @ReqOlderThanMin = 0 THEN GETDATE() ELSE DATEADD(MINUTE,-@ReqOlderThanMin,GETDATE()) END
49    AND [open_transaction_count] = CASE WHEN @HasOpenTran = 'Y' AND [open_transaction_count] > 0 THEN [open_transaction_count] ELSE 0 END;
50
51OPEN KillSPIDCursor;
52FETCH NEXT FROM KillSPIDCursor INTO @SPID;
53
54WHILE @@FETCH_STATUS = 0
55BEGIN
56    SET @ExecSQL = 'KILL ' + CAST(@SPID AS VARCHAR(5)) + ';';
57    EXEC (@ExecSQL);
58    FETCH NEXT FROM KillSPIDCursor INTO @SPID;
59END;
60
61CLOSE KillSPIDCursor;
62DEALLOCATE KillSPIDCursor;

Variable Declarations

The script declares eight filter variables and two working variables (@SPID and @ExecSQL). The filter variables all default to empty strings or zero, meaning the script will match all eligible sessions unless you set a specific value. @Confirm is a BIT type and defaults to 0, which acts as the safety switch described next.

Safety Switch: @Confirm

1SET @Confirm = 0;

When @Confirm is 0, the script prints a message and exits immediately with RETURN. No sessions are killed. This prevents accidental execution if you run the script without reviewing the filter settings first. Change this to 1 only when you are ready to execute.

Filter Parameters

Note that @ForLogin and @OmitLogin are mutually exclusive in intent. The cursor WHERE clause handles the three combinations: both empty (all logins), only @OmitLogin set (exclude that login), or @ForLogin set (include only that login).

Cursor Definition and Session Query

1DECLARE KillSPIDCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
2  SELECT DISTINCT [session_id]
3  FROM   [master].[sys].[dm_exec_sessions]
4  ...

The cursor is declared as LOCAL STATIC READ_ONLY FORWARD_ONLY. This is best practice for a cursor that only reads data in one direction — it keeps memory use low and avoids side effects on the underlying data.

The query reads from sys.dm_exec_sessions, which shows all active sessions on the SQL Server instance. Key filters applied in the WHERE clause are:

• [session_id] <> @@SPID — excludes the current session so the script cannot kill itself
• [is_user_process] = 1 — excludes system sessions (background processes, system SPIDs)
• [login_name] NOT IN (SELECT [service_account] FROM sys.dm_server_services WHERE [status] = 4) — excludes service accounts for running SQL Server services, protecting the SQL Agent and other services

The last_request_start_time filter uses DATEADD(MINUTE, -@ReqOlderThanMin, GETDATE()) to find sessions whose last request started before the cutoff time. When @ReqOlderThanMin is 0, the expression resolves to GETDATE(), which matches all sessions.

Cursor Loop and KILL Execution

1WHILE @@FETCH_STATUS = 0
2BEGIN
3    SET @ExecSQL = 'KILL ' + CAST(@SPID AS VARCHAR(5)) + ';';
4    EXEC (@ExecSQL);
5    FETCH NEXT FROM KillSPIDCursor INTO @SPID;
6END;

The loop fetches one session ID at a time. The KILL statement cannot be parameterized directly, so the script builds a short dynamic SQL string and executes it with EXEC(). After killing each session, it fetches the next row. The loop ends when @@FETCH_STATUS is no longer 0, meaning the cursor has no more rows.

Cursor Cleanup

1CLOSE KillSPIDCursor;
2DEALLOCATE KillSPIDCursor;

Closing and deallocating the cursor releases the server resources associated with it. Always include these two statements when you are done with a cursor.

Key Benefits and Use Cases

• Dry-run by default. The @Confirm = 0 default means you can review the script, set your filters, and not worry about accidentally killing sessions when you first open it.
• Target a single database. Set @ForDatabase = N'YourDB' to restrict kills to connections on one database without touching others on the same instance.
• Protect a specific login. Use @OmitLogin to kill everything except an application service account or your own login.
• Clean up sleeping sessions. Set @SPIDState = 'S' to kill only sleeping sessions, leaving active queries untouched.
• Kill blocked or blocking sessions. Combine @HasOpenTran = 'Y' with other filters to find sessions holding open transactions that are likely blocking other work.
• Idle session cleanup. Set @ReqOlderThanMin to a value like 60 to disconnect sessions that have made no request in the last hour.
• Service account safety. The automatic exclusion of SQL Server service accounts protects the SQL Agent and other services from being disconnected.

Performance Considerations

Impact of KILL on active transactions. When you kill a session that has an open transaction, SQL Server rolls back that transaction. For long-running transactions, rollback can take considerably longer than the original transaction took to run. Check sys.dm_exec_sessions for open_transaction_count and sys.dm_tran_active_transactions before killing sessions with open transactions.

Cursor overhead. For a typical number of sessions (dozens to low hundreds), the cursor approach is fine. If you are running this on an instance with thousands of sessions, the cursor will work but will take longer. Monitor for performance impact on busy production instances.

ALTER DATABASE SET SINGLE_USER as an alternative. For the specific use case of disconnecting all users before a database operation (restore, detach, drop), ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE is often simpler. It rolls back all active transactions and drops all connections in a single statement. The script in this article is more useful when you need selective filtering rather than a full disconnect of one database.

Avoid killing system SPIDs. The script already filters these out, but as a reminder: never manually kill SPIDs with IDs below 51. These are internal SQL Server system processes.

Check before you kill. Before running with @Confirm = 1, run the inner SELECT from the cursor against sys.dm_exec_sessions by itself first to verify which session IDs would be affected.

Practical Tips

Previewing what will be killed. Copy the SELECT DISTINCT [session_id] query from inside the cursor definition and run it directly after setting your filter variables. This shows exactly which SPIDs the script would kill before you set @Confirm = 1.

Combining filters. The filters work together with AND logic. For example, setting @ForDatabase = N'Sales' and @SPIDState = 'S' kills only sleeping sessions on the Sales database. Think through the combination you need before running.

Killing sessions for a login across all databases. Leave @ForDatabase empty and set @ForLogin to the login name. The script will kill all sessions for that login regardless of which database they are connected to.

Scheduling idle session cleanup. You can wrap this script in a SQL Agent job to run on a schedule. Set @Confirm = 1 and @ReqOlderThanMin to your threshold (for example, 120 for two hours) to automatically remove stale connections on a recurring basis.

Unicode login names. The login filter variables are NVARCHAR(128) to match the data type in sys.dm_exec_sessions. Use the N'' prefix when setting login names to avoid implicit conversion issues, especially if logins contain non-ASCII characters.

After a restore scenario. When preparing a database for a restore, set @ForDatabase to the target database name and @Confirm = 1. Run the script to clear all connections, then immediately run your restore statement.

Conclusion

This script gives SQL Server DBAs a flexible and safe way to kill multiple sessions in a single operation. The @Confirm safety switch prevents accidental execution, and the seven filter parameters cover the most common scenarios: targeting by login, database, session state, open transactions, or request age. The automatic exclusion of the current session and SQL Server service accounts adds a layer of protection that helps avoid operational mistakes. Keep this script in your DBA toolkit for maintenance windows, blocking investigations, and connection management tasks.

References

• VladDBA SQL Server Scripts GitHub Repository — Source of the KillSPIDs.sql script with filtering options for targeted session management
• Microsoft Docs: sys.dm_exec_sessions — DMV used to identify active sessions and retrieve session metadata including login name, database, status, and transaction count
• Microsoft Docs: KILL (Transact-SQL) — Official documentation for the KILL command including syntax, behavior with open transactions, and restrictions