Get Progress of currently running SQL Agent jobs

/*

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%';