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,
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)

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


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

      SELECT sqltext.TEXT,
      FROM sys.dm_exec_requests req
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
      where DB_NAME(req.database_id) = ‘master’


    • 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


  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.


  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?


  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

    Liked by 1 person

  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


  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


  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.


    • — Variable Declarations
      DECLARE @PreviousDate datetime
      DECLARE @Year VARCHAR(4)
      DECLARE @Month VARCHAR(2)
      DECLARE @MonthPre 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],
      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


  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


  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.


  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.


  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…


  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.


  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?


  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.


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


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


  20. select
    , 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’
    ,’RA MANAGER’)
    order by batch_duration desc


  21. SELECT sqltext.TEXT,
    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.


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