SQL Agent - Get Progress of currently running SQL Agent jobs

Applicability:

                 SQL Server 2000:        Not Tested 
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:  Supplied by Max Vernon
Date:      3 Sep 2014

Description

 Thanks to Max Vernon for this.  I supplied him a copy of the Get Restore Progress code and he very quickly reciprocated with this variant that produces a similar report for all SQL Agent jobs currently running.  Very useful when you are using Central Management Server or for a server that has a lot of scheduled jobs.

Code

 /*

      Shows the progress of any running SQL Agent jobs
*/
DECLARE @Actions TABLE
(
      
ActionID INT
      
, ActionText VARCHAR(50)
);
INSERT INTO @Actions (ActionID, ActionText)
VALUES ( 1,'Quit with success')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (2,'Quit with failure')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (3,'Go to next step')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (4, 'Go to step on_success_step_id');

SELECT JobName = sj.name
      
, StepName = sjs.step_name
      
, OnSuccessAction = ASuccess.ActionText
      
, OnFailAction = AFail.ActionText
      
, r.blocking_session_id
      
, r.estimated_completion_time
      
, r.last_wait_type
      
, r.start_time
      
, s.HOST_NAME
      
, SQLStatement = SUBSTRING(t.TEXT, ISNULL(r.statement_start_offset,0), CASE WHEN ISNULL(r.statement_end_offset,0) = -1 THEN LEN(t.TEXT) ELSE ISNULL(r.statement_end_offset,0) END - ISNULL(r.statement_start_offset,0))
FROM sys.dm_exec_sessions s WITH (NOLOCK)
      
INNER JOIN sys.dm_exec_requests r WITH (NOLOCK)ON s.session_id = r.session_id
      
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
      
INNER JOIN msdb.dbo.sysjobsteps sjs WITH (NOLOCK) ON SUBSTRING(s.program_name,30,34) = CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sjs.job_id), 1)
      
INNER JOIN msdb.dbo.sysjobs sj (NOLOCK) ON sjs.job_id = sj.job_id
      
INNER JOIN @Actions AFail ON sjs.on_fail_action = AFail.ActionID
      
INNER JOIN @Actions ASuccess ON sjs.on_fail_action = ASuccess.ActionID
WHERE s.program_name LIKE 'SQLAgent%';

ċ
Get progress of all SQL Agent Jobs.sql
(2k)
Andy Hughes,
Sep 3, 2014, 10:05 AM
Comments