Is there a way to find the line of code a query is running on in a SQL Server?-CodePudding

I have a long running query session, I'd like to find out what line (piece of code in a sproc) it's currently running in SQL Server?

In an active user session, there's no query plan available that I can look into for a user process who ran "select into" or "Insert" inside a sproc. I tried to look into the sys.dm_exec_requests to see if there's such a linenum column for that active session but there isn't.

Thanks for any help.

CodePudding user response：

sys.dm_exec_requests only returns the sql_handle, you need to pass that to dm_exec_sql_text to get the text. Then you can use some calculation to work out the text of the running statement

SELECT
  SUBSTRING(st.text, (r.statement_start_offset / 2)   1,   
        ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE r.statement_end_offset  
          END - r.statement_start_offset) / 2)   1) AS statement_text  
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id = @someSPID;