SQL SERVER – Find Currently Running Query – T-SQL

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)

About these ads

41 thoughts on “SQL SERVER – Find Currently Running Query – T-SQL

  1. 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.

    Like

    • I think you can add filter (where clause) of database as follows into this query

      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
      where DB_NAME(req.database_id) = ‘master’

      Like

    • Add one more join to query as follow
      INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id

      and column in select list as s.login_name

      Like

  2. 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.

    Like

  3. 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?

    Like

  4. 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

    Like

  5. Pingback: SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query Journey to SQL Authority with Pinal Dave

  6. “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

    Like

  7. 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

    Like

  8. 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.

    Like

    • – Variable Declarations
      DECLARE @PreviousDate datetime
      DECLARE @Year VARCHAR(4)
      DECLARE @Month VARCHAR(2)
      DECLARE @MonthPre VARCHAR(2)
      DECLARE @Day VARCHAR(2)
      DECLARE @DayPre VARCHAR(2)
      DECLARE @FinalDate INT

      — Initialize Variables
      SET @PreviousDate = DATEADD(dd, -7, GETDATE()) — Last 7 days
      SET @Year = DATEPART(yyyy, @PreviousDate)
      SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
      SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
      SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
      SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
      SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

      — Final Logic
      SELECT j.[name],
      s.step_name,
      h.step_id,
      h.step_name,
      h.run_date,
      h.run_time,
      h.sql_severity,
      h.message,
      h.server
      FROM msdb.dbo.sysjobhistory h
      INNER JOIN msdb.dbo.sysjobs j
      ON h.job_id = j.job_id
      INNER JOIN msdb.dbo.sysjobsteps s
      ON j.job_id = s.job_id
      AND h.step_id = s.step_id
      WHERE h.run_status = 0 — Failure
      AND h.run_date > @FinalDate
      ORDER BY h.instance_id DESC

      Like

  9. 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

    Like

  10. Pingback: SQL Server – Find Currently Running Queries « Thameem

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  12. 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.

    Like

  13. 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.

    Like

  14. 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…

    Like

  15. 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.

    Like

  16. 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

    Like

  17. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s