Home » T-SQL script to kill multiple sessions in one go

T-SQL script to kill multiple sessions in one go

by Vlad Drumea
0 comment

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

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.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.