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 (
http://blog.SQLAuthority.com
)
We can get this even by profiler too, right?
But is there anyway i get the queries executed only on a database rather than server instance itself?
This will help if when we are trying to understand a big application that talks to a database.
What about adding the user to the query?
sorry to ask my question here.
Just want to know how to retrieve data faster from sql server 2005 in VB.net 2005? Any idea? thanks.
When I try running this script I get the Message “sql_handle” is not a recognized table hints option. I am running 2005 but compatability mode is set to 80. Is there a way round this?
This works only at particular instance of time.
I want to record all the sql running/finished, its cpu time, etc
Hi
when i ran this query.it shows me the same query in the output.I think result expected would be a different query.
Does anybody know a query for SQL Server 2000?
Thank you!
Hi
When i run the above query, the same i got as the current running query.
Is there are no queries running currently other than the above query?
If some other queries were also running, then how can it be captured?
Thanks in advance
Pingback: SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query Journey to SQL Authority with Pinal Dave
is there any way of seeing the parameters being passed with the exec ?
“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
currently running query error
thanks a lot.. this query helped me solve an issue :)
Hi Pinal,
IS it possible to retrive the details of currently running ssis package information by using query that is stored in msdb ,but not scheduled in sql agent job.
Thanks in Advance
Prasad
sp_usrinputbuffer
@tandy,
what is exactly ‘sp_usrinputbuffer’ for ??
Hi Hendra!
It´s a procedure to identify the exact point of execution of a statement.
You can find the complete code of this procedure on:
http://pareaki.com/bloqueios.txt
Good luck!
Regards,
Tandy
I use this on occassion however a added “WHERE session_id @@SPID” so that I do not self
This article is very helpful to me, thanks to pinaldave
thanks a lot it saved most of the time for today
Thanks for this article, but i have another query, for example i have stored procedure named A running having function named B, if i execute procedure A, using this query, at some stage this will show function B is executing rather procedure A, please give me any solution for finding procedure A while procedure executing function B.
above query works fine in sql server 2005, but how about 2000. Please help me.
Hi Pinaldave,
i have one dought, how findout current(Today) running multipule sql agent job list?
regards,
Harish
Hi Pinaldave,
i have one dought, how findout current(Today) running multipule job list?
regards,
Harish
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
Pingback: SQL Server – Find Currently Running Queries « Thameem
Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority
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…