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)
59 Comments. Leave new
Hi,
While I am running the above query in SQL server 2000. I’m getting the following error:
Incorrect syntax near ‘APPLY’.
can any one suggest me…?
Thanks in advance
Thanks very usefull
Hi Pinal,
Please let us know how we can get all the SQL quries executed during a time period.
Thanks
Hi Pinal,
We have a setup in our environment which depends completely on Stored Procedures, we have a problem out like a stored procedure is executing continuously in the backend for every second, i have checked the jobs but i could find nothing scheduled in jobs, even windows event viewer and activity monitor could not help me, this was shown in profiler that a particular sp is running continuosly, so could you please tell me where i can find the solution for it, i don’t want that SP to run continuously.
Dear Sir, Thank you for your wondeful blog. It has been helping me a lot with my project. I have a question regarding this method. I ran the above query to see how many queries in my batch have successfully completed and which one is running right now. But it gives me the list of all queries in the batch and not the query which is actually running right now. Could you please help me with that. I would really appreciate it.
Hi Pinal,
Is there specific reason for using sys.dm_exec_sql_text(sql_handle) over fn_get_sql(sql_handle)?
select * from fn_get_sql(sql_handle)
select * from sys.dm_exec_sql_text(sql_handle)
Both the queries give the same result though…
Thanks helps us out to recover a server running 100% cpu by killing the guilty processes
Hi Pinal,
Suppose 2 of the application servers are firing queries to a common database server, can we trace or find out which query was fired from which application server.
Adding more columns to the sql profiler served my purpose with different machines which acutally were firing the query.
Thanks :)
This is a helpful little script. Question – the ‘text’ column often has compound statements and in many cases even full store-procs. I want to display them in readable format for monitoring, but that field looses the new-lines, so if the first line was a comment starting with — – the whole thing looks like a comment.
Is there a way to get the original SQL with the format, or at least with the new-lines, so I can present it?
thanks
Hello, I run a big batch of sql queries. This query gives me the whole batch but not the particular query which is actually running currently. Is there any way to do it. I would really appreciate your help. Thank you so much.
i would add
WHERE req.session_id @@spid
in order not to see self execution,
Very useful example thank you!!!
to see the time in seconds, change to this line:
req.total_elapsed_time / 1000 AS Total_Elapsed_Time_Sec,
Hi Pinal
I have one question in my 2008 r2 (sp3) server express edition at the mid night log file gets
full. when i checked one open transaction is there but am not able to find out which query is running in that transaction .
database in simple recovery model.
please reply asap.
Thanks in advance
Might I recommend, where you mention KILL [spid] that you also mention that the query will be rolled back, rather than instantly killed.
A SELECT statement will KILL quickly, but the rollback that results from killing an update query can be hours (depending on how long the original query was running before the KILL was issued).
select
P.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), ‘1900-01-01’),
121), 12) as ‘batch_duration’
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in (‘background’, ‘sleeping’)
and P.cmd not in (‘AWAITING COMMAND’
,’MIRROR HANDLER’
,’LAZY WRITER’
,’CHECKPOINT SLEEP’
,’RA MANAGER’)
order by batch_duration desc
Now the column batch_duration becomes a character and the sorting may be affected by this. Read this to understand how you should use alias names
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 using this query i am getting the T-sql query’s which are running in the current session however i am not getting what are the queries running currently on a server… could you please help me out to find the queries which are running on a server.
Hi Sir,
Is this query helps to get count of total queries executing in the database??
Thanks for the query..
SELECT sqltext.TEXT,
wait_type,
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
Adding wait_type can be helpful to know why a query is suspended.
can anyone help me out with my needs please” I’m looking for a single file that does these things in order.
1. Declare a variable to hold a job name (so it’s easy to change later)
2. Run a query to check whether that job declared in #1 is currently running
3. Set up an if loop based on #2 above. If the job is running, execute the stored procedure that kills it.”
i am a new SQL DBA i am having hard time to write a query based on that ,please help me out
Thanks