This is the script which I always had in my archive. Following script find out which are the queries running currently on your server.
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
While running above query if you find any query which is running for long time it can be killed using following command.
KILL [session_id]
Reference : Pinal Dave (https://blog.sqlauthority.com)
60 Comments. Leave new
Thank you, the query was really helpful!
Thank you :)
Unfortunately, if a stored proc is currently running, it shows the full test of the SP, not which command within the proc is executing.
To get all running query, but your own add
where req.session_id @@SPID
Thank you! That was really helpfull.
Hope you are doing well. Is it possible to get record drop count at each where clause level in a single query. I want to avoid writing different queries for it and creating intermediate tables to do the same.
Madhu
Thank’s, it’s helpful
thanks you
I am running a long query (which will take long time to complete) and i want to know when it is completed and also want see the progress … Is that possible..?
Need to know that, too!
Thanks! This has been helpful.
Is this still completely valid in 2021?
Added to this slightly:
SELECT sqltext.TEXT,
req.session_id,
req.user_id,
req.status,
sess.host_name,
sess.login_name,
sess.program_name,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess on sess.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext