My SQL Agent Job Monitor

This is what I use to monitor a single job while it is running. There are a couple of critical jobs that I need to monitor where I use this script. The queries below provide:

• current job status
• job steps completed
• estimate of remaining time for the job to complete (needs some improvement)
• remain steps to be completed with run stats from yesterday

Specify the job_id to monitor: SELECT job_id FROM msdb.dbo.sysjobs

DECLARE @JOB_ID UNIQUEIDENTIFIER = '28D22C4A-F708-45C3-8917-2B08EBA5A550';

-- Check if the job is currently running. Look at start_execution_date to make sure

-- it started today. If it's still running, stop_execution_date IS NULL

SELECT 

start_execution_date

, last_executed_step_id

, last_executed_step_date

, stop_execution_date

, job_history_id

, next_scheduled_run_date

, a.job_id

, j.[name]

FROM msdb.dbo.sysjobactivity a

JOIN msdb.dbo.sysjobs j

ON j.job_id = a.job_id

WHERE session_id = (

SELECT MAX(session_id)

FROM msdb.dbo.syssessions

) 

AND a.job_id = @JOB_ID;

----------------------------------------------------------------------------------------

--

-- Show history for _Daily DataLoad and Reports Run for today, the estimated remaining 

-- time for the job to complete, and the detail for the job steps remaining.

-- Estimates are based on the prior day.

--

-- run_status:

-- 0 = Failed

-- 1 = Succeeded

-- 2 = Retry

-- 3 = Canceled

-- 4 = In Progress

DECLARE 

@RUN_DATETIME DATETIME = CONVERT(DATE, GETDATE())

, @RUN_DATE INT 

, @YESTERDAY INT

, @LAST_STEP_ID_COMPLETED INT;

SET @RUN_DATE = FORMAT(@RUN_DATETIME, 'yyyyMMdd');

SET @YESTERDAY = FORMAT(DATEADD(day, -1, @RUN_DATETIME), 'yyyyMMdd');

SELECT 

msdb.dbo.agent_datetime(run_date, run_time) start_time

, DATEADD(

second

, [run_duration] / 10000 * 3600 + -- convert hours to seconds

([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds

([run_duration] % 10000) % 100 -- get seconds

, msdb.dbo.agent_datetime(run_date, run_time)) end_time

, ([run_duration] / 10000 * 3600 + -- convert hours to seconds

([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds

([run_duration] % 10000) % 100) / 60 AS [MINUTES_DURATION] -- get seconds

, step_id

, step_name

, sql_message_id

, run_status

, run_date

, run_time

, run_duration

, retries_attempted

, job_id

FROM msdb.dbo.sysjobhistory h

WHERE h.run_date = @RUN_DATE

--AND h.[run_status] <> 2

AND [job_id] = @JOB_ID

ORDER BY step_id DESC

SELECT 

@LAST_STEP_ID_COMPLETED = MAX(step_id)

FROM msdb.dbo.sysjobhistory h

WHERE h.run_date = @RUN_DATE

AND [job_id] = @JOB_ID

;WITH CTE_REMAINING_STEPS AS (

SELECT 

msdb.dbo.agent_datetime(run_date, run_time) start_time

, DATEADD(

second

, [run_duration] / 10000 * 3600 + -- convert hours to seconds

([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds

([run_duration] % 10000) % 100 -- get seconds

, msdb.dbo.agent_datetime(run_date, run_time)) end_time

, ([run_duration] / 10000 * 3600 + -- convert hours to seconds

([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds

([run_duration] % 10000) % 100) / 60 AS [MINUTES_DURATION] -- get seconds

, step_id

, step_name

, sql_message_id

, run_status

, run_date

, run_time

, run_duration

, retries_attempted

, job_id

FROM msdb.dbo.sysjobhistory h

WHERE [job_id] = @JOB_ID

AND h.run_date = @YESTERDAY

AND step_id > @LAST_STEP_ID_COMPLETED

)

SELECT 

SUM([MINUTES_DURATION]) AS [MINUTES_REMAINING]

FROM CTE_REMAINING_STEPS;

SELECT 

msdb.dbo.agent_datetime(run_date, run_time) start_time

, DATEADD(

second

, [run_duration] / 10000 * 3600 + -- convert hours to seconds

([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds

([run_duration] % 10000) % 100 -- get seconds

, msdb.dbo.agent_datetime(run_date, run_time)) end_time

, ([run_duration] / 10000 * 3600 + -- convert hours to seconds

([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds

([run_duration] % 10000) % 100) / 60 AS [MINUTES_DURATION] -- get seconds

, step_id

, step_name

, sql_message_id

, run_status

, run_date

, run_time

, run_duration

, retries_attempted

, job_id

FROM msdb.dbo.sysjobhistory h

WHERE [job_id] = @JOB_ID

AND h.run_date = @YESTERDAY

AND step_id > @LAST_STEP_ID_COMPLETED

ORDER BY step_id;