Find and kill long running MS SQL Server queries

I have to admit this is cargo cult SQL to me, but here’s how to find running queries, sorted by their total elapsed time:

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
ORDER BY [req].[total_elapsed_time] DESC

To kill a given query, use:

kill <session_id>

..without the brackets <>, where session_id is the corresponding column output from the query above

If you want to kill all long running queries in a single go, try something like this:

DECLARE @cmd varchar(8000) = '';
SELECT @cmd = @cmd + 'kill ' + CONVERT(varchar(5), req.session_id) + ';'
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where req.total_elapsed_time > 15000

SELECT @cmd;

EXEC(@cmd);

Adjust the ‘15000’ (milliseconds) timeout to a reasonable time for your application.

You’re welcome.