Tag Archives: how to kill queries in sql server

Killing running processes in SQL Server

Hey Guys!
This particular code should be handled with great care as you might end up killing production processes. Be very careful in firing this SQL.
First list out your running processes with the help of below query:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

The results returned by above query contains many columns of interest but we are particularly interested in ‘Session_ID’ as this column will be used in the code to kill other running processes.
Here goes the code:
DECLARE @tbl table
(
id int identity(1,1),
sessionid int
)

INSERT INTO @tbl (sessionid)
SELECT
req.session_id
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

DECLARE @rowcnt int, @sesid int, @sql nvarchar(max)
SELECT @rowcnt = count(*) from @tbl

WHILE (@rowcnt>0)
BEGIN
SELECT @sesid = sessionid FROM @tbl WHERE id = @rowcnt
SET @sql = ‘kill ‘ + cast(@sesid as nvarchar(5))
IF @sesid NOT IN (51,63) — Pass session ids of those processes that you don’t want to kill
EXEC sp_executesql @sql

set @rowcnt-=1

END

Enjoy!!
Happy Coding!

Advertisements