In my last blog I talked about how to kill a session in SQL Server. In this blog I will talk about how to kill multiple sessions in SQL Server.
The blog just explains a way to kill multiple sessions as and when required it doesn’t promotes it as a best practice. Don’t run this on production server unless you know what you are doing.
The below query generates the kill statements on the fly for the multiple session which are to be killed.
-- How to kill multiple sessions
SELECT 'KILL ' + CAST(session_id as varchar(100)) AS Sessions_to_kill
FROM sys.dm_exec_requests where session_id in (54,57,58)
Now, we have list of sessions we want to kill as shown below.
Another way is to execute the KILL query as a dynamic query instead of getting the list. However, it’s better to check the sessions again before killing them.
The next step is to copy and paste the list in a new query window and execute the KILL command.
And remember; don’t try this on production server