SQL SERVER – Find Currently Running Query – T-SQL « Journey to SQL Authority with Pinal Dave

Feeds:: Posts; Comments

SQL SERVER – Find Currently Running Query – T-SQL

January 7, 2009 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology | 11 Comments

11 Responses

on January 7, 2009 at 9:39 am | Reply Ram

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.
on January 7, 2009 at 10:42 pm | Reply Derek W

What about adding the user to the query?
on January 9, 2009 at 7:16 am | Reply scsfdev

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.
on January 15, 2009 at 3:43 pm | Reply Craig

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?
on January 27, 2009 at 9:52 am | Reply Sridhar

This works only at particular instance of time.

I want to record all the sql running/finished, its cpu time, etc
on February 22, 2009 at 4:59 pm | Reply Pramod

Hi
when i ran this query.it shows me the same query in the output.I think result expected would be a different query.
on April 15, 2009 at 4:03 am | Reply Rene Cum

Does anybody know a query for SQL Server 2000?

Thank you!
on July 29, 2009 at 2:14 pm | Reply Keerthi

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
on August 2, 2009 at 10:41 am | Reply SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Find Currently Running Query – T-SQL [...]
on August 20, 2009 at 10:09 pm | Reply rosie

is there any way of seeing the parameters being passed with the exec ?
on November 18, 2009 at 3:22 pm | Reply arul

“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