Ever needed a script to kill multiple sessions on your SQL Server instance, but still required some level of control over what was being targeted?
This script might come in handy.
Brief intro
This script gathers the SPIDs that match the filtering conditions and proceeds to kill those sessions. If the filters are left unchanged (except for @Confirm which needs to be set to 1 for the script to do its job), the script will kill all sessions aside from system initiated ones and the SPID currently executing the kill script.
Filter variables and usage
@Confirm – default 0, will exit execution without affecting any sessions if it’s not set to 1. This acts more as a precaution against just running the script directly without checking the filters first.@ForLogin – default '' (empty string). If set to a valid login name, the script will only target SPIDs belonging to said login.@SPIDState – default '' and will target sessions in any state, other accepted values are S (will only target sleeping sessions) and R (will only target sessions actively running at script execution time).@OmitLogin – default '' . If set to a valid login name, the script will not kill SPIDs belonging to said login.@ForDatabase – default '' . If set to a valid database name, the script will only target sessions whose current database matches the name provided.@HasOpenTram – default '' . If set to Y will target sessions with open transactions. Can be combined with @SPIDState = 'S' to target sleeping sessions with opened transactions that might be caused by SET IMPLICIT_TRANSACTIONS ON.@ReqOlderThanMin – default 0. The script targets sessions whose last_request_start_time is older than or equal to the number of minutes set for this variable.
Multiple filters can be used for a more targeted approach (e.g killing all sessions belonging to User1, on SomeDatabase, and whose last requests started 3+ minutes ago)
The script
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | USE [master] GO /* Variable declaration */ DECLARE @SPID SMALLINT ,@ExecSQL VARCHAR(11) ,@Confirm BIT ,@ForLogin NVARCHAR(128) ,@SPIDState VARCHAR(1) ,@OmitLogin NVARCHAR(128) ,@ForDatabase NVARCHAR(128) ,@HasOpenTran VARCHAR(1) ,@ReqOlderThanMin INT; /* Filters */ SET @Confirm = 0; /* Just a precaution to make sure you've set the right filters before running this, switch to 1 to execute */ SET @ForLogin = N''; /* Only kill SPIDs belonging to this login, empty string = all logins */ SET @SPIDState = ''; /* S = only kill sleeping SPIDs, R = only kill running SPIDs, empty string = kill SPIDs regardless of state */ SET @OmitLogin = N''; /* Kill all SPIDs except the login name specified here, epty string = omit none */ SET @ForDatabase = N''; /* Kill only SPIDs hitting this database, empty string = all databases */ SET @HasOpenTran = '' /* If set to Y will target sessions with open transactions, can be combined with @SPIDState = 'S' to target sleeping sessions with opened transactions that might be caused by SET IMPLICIT_TRANSACTIONS ON. empty string (default) = 0 open transactions*/ SET @ReqOlderThanMin = 0; /* Kill SPIDs whose last request start time is older than or equal to the value specified (in minutes), 0 = the moment this query is executed */ IF (@Confirm = 0) BEGIN PRINT '@Confirm is set 0. The script has exited without killing any sessions.' RETURN END DECLARE KillSPIDCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT [session_id] FROM [master].[sys].[dm_exec_sessions] WHERE [login_name] = CASE /* Get all SPIDs */ WHEN @OmitLogin = N'' AND @ForLogin = N'' THEN [login_name] /* Get all SPIDs except for the ones belonging to @OmitLogin */ WHEN @OmitLogin <> N'' AND @ForLogin = N'' THEN (SELECT DISTINCT [login_name] FROM [master].[sys].[dm_exec_sessions] WHERE [login_name] <> @OmitLogin) /* Get all SPIDs belonging to a specific login */ WHEN @ForLogin <> N'' THEN @ForLogin END AND [session_id] <> @@SPID /* Exclude this SPID */ AND [is_user_process] = 1 /* Target only non-system SPIDs */ AND [database_id] = CASE WHEN @ForDatabase <> N'' THEN DB_ID(@ForDatabase) ELSE [database_id] END AND [login_name] NOT IN (SELECT [service_account] FROM [master].[sys].[dm_server_services] WHERE [status] = 4) AND [status] = CASE WHEN @SPIDState = 'S' THEN N'sleeping' WHEN @SPIDState = 'R' THEN N'running' ELSE [status] END AND [last_request_start_time] <= CASE WHEN @ReqOlderThanMin = 0 THEN GETDATE() WHEN @ReqOlderThanMin > 0 THEN DATEADD(MINUTE,-@ReqOlderThanMin,GETDATE()) END AND [open_transaction_count] = CASE WHEN @HasOpenTran = 'Y' AND [open_transaction_count] > 0 THEN [open_transaction_count] ELSE 0 END; OPEN KillSPIDCursor; FETCH NEXT FROM KillSPIDCursor INTO @SPID; WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecSQL = 'KILL ' + CAST(@SPID AS VARCHAR(5)) + ';'; EXEC (@ExecSQL); FETCH NEXT FROM KillSPIDCursor INTO @SPID; END; CLOSE KillSPIDCursor; DEALLOCATE KillSPIDCursor; |
Reference: Microsoft’s documentation for sys.dm_exec_sessions and Aaron Bertrand’s post about cursor options
This script can also be found in my GitHub repository.
Disclaimer: This script is provided as-is and I am not responsible for any production impact potentially brought on by improper usage of this script.