How to Monitor a Long Running SQL Agent Job

 DECLARE @JOB_ID UNIQUEIDENTIFIER = '42602363-FC74-44BE-ADB1-FA5FEA1917DC'

-- 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 *

FROM msdb.dbo.sysjobactivity

WHERE session_id = (

SELECT MAX(session_id)

FROM msdb.dbo.syssessions

) 

AND job_id = @JOB_ID

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

--

-- Show history for job 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_DATE INT = FORMAT(GETDATE(), 'yyyyMMdd')

, @LAST_STEP_ID_COMPLETED INT;

--DECLARE @RUN_DATE INT = '20210105';

DECLARE @YESTERDAY INT = @RUN_DATE - 1;

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

, *

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

, *

FROM msdb.dbo.sysjobhistory h

WHERE [job_id] = @JOB_ID -- Weekday DataLoad and Reports

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

, *

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; 

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