How to find out what are the currently running queries with MSSQL 2005

MSSQL 2005 keep tracks of all the running queries with 2 tables:

  1. sys.dm_exec_requests
  2. sys.dm_exec_sql_text

This will give you enough info to investigate the big jobs, and how they have been running.

SELECT reqs.session_id AS 'Session',
       reqs.request_id AS 'Request',
       reqs.start_time AS 'Start',
       CURRENT_TIMESTAMP AS 'Now',
       reqs.status AS 'Status',
       reqs.cpu_time AS 'CPU time',
       reqs.total_elapsed_time AS 'Total elapsed time',
       sqlt.TEXT AS 'Query'
FROM sys.dm_exec_requests reqs
     cross apply sys.dm_exec_sql_text(reqs.sql_handle) sqlt;
go
Session Request     Start                   Now                     Status                         CPU time    Total elapsed time Query
------- ----------- ----------------------- ----------------------- ------------------------------ ----------- ------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
52      0           2007-07-16 18:49:45.483 2007-07-16 18:49:45.483 running                        0           4                  select reqs.session_id as 'Session',
       reqs.request_id as 'Request',
       reqs.start_time as 'Start',
       current_timestamp as 'Now',
       reqs.status as 'Status',
       reqs.cpu_time as 'CPU time',
       reqs.total_elapsed_time as 'Tot

(1 row(s) affected)
  • sys.dm_exec_sql_text has almost the actual text of the currently executing queries. It's almost.
  • It's only the first 256 characters.