Script to get current running query from Stored Procedure / Trigger / Function

I have written small script which gives you the current running query from stored procedure, trigger or function. Working on big database processing I ofter come across stored procedures (SPs) which takes hours to complete. These SPs has many small queries (INSERT/UPDATE/DELETE/MERGE) or call to other SP or call function. Mostly I use RAISEERROR WITH NOWAIT or PRINT to know the progress of SP but there are some instances where I cannot change SP to add progress reporting statement. So wrote below script to know the current running query. It is very useful to know current running query and helped me in many data processing problems.

SELECT

  SUBSTRING(B.[text]

           , (A.statement_start_offset / 2) + 1

           , (((CASE WHEN A.statement_end_offset <= 0 THEN DATALENGTH(B.[text])

                      ELSE A.statement_end_offset END)

                  - A.statement_start_offset) / 2 ) + 1

           ) AS statement_text

FROM   sys.dm_exec_requests A

       CROSS APPLY sys.dm_exec_sql_text(sql_handle) B

WHERE  session_id = 56 --Use you session id here.

GO

Please note that above script will only tells you which query is running, not the actual progress of the query. Unfortunatly there is no way to know the actual progress of the query :(.

You can use Activity Monitor from management studio or use sp_who2 or below query to know the session id

SELECT * FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Plesae comment if you like this script or if have better script/way or if you find any error in this script or any aditional information about this.

MS SQL 2008 | MS SQL 2008 R2 | MS SQL 2012 | T-SQL | T-SQL Tutorials | Learn T-SQL | | Microfot SQL Server | MS SQL Server Admministration | How to know current running query | How to know current running query from SP