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%';
|
 Updating...
Andy Hughes, Sep 3, 2014, 10:05 AM
|