SQL Server - TSQL to Detect missed Agent jobs due

Question

I am trying to work out the TSQL to detect if a JOB has overran and missed its next schedule.

Scenario: Job is scheduled to run every hour (there may be more than one schedule enabled), but due to some reason such as network delays, the job stops taking 10 minutes and is now running for 90 minutes and therefore missing it's hourly start.

There is a table called msdb.dbo.sysjobactivity, which has a column called next_scheduled_run_date but this date only gets update after the currently running job has finished. This is the same behaviour as the Job Activity monitor, the next run date only updates after the current job finishes.

msdb.dbo.sysjobactivity is updated as per the details in: https://dba.stackexchange.com/questions/22644/how-and-when-does-sql-agent-update-next-run-date-next-run-time-values

Question: How can I calculate the next scheduled run for a job, based on the current job start time while the job is running. There may be more than a single schedule assigned.

Any pointers appreciated.

Answer 1

This is a tricky one. Firstly the stackexchange link that you included is excellent. I followed that as well as doing my own trace. The code below will set you in the right direction. You may need to do detailed testing, I found that I had some timing issues when the answer was to the same minute but the seconds were slightly out. You can of course modify this as needed.

here is the first draft of the code which should put you in the right direction

declare @jobname [sysname] = 'Check'
Select   
     nextOne = min(
        case freq_subday_type 
            when 8 then dateadd(hour  , (Datediff(hour  , [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time), getdate())/freq_subday_interval + 1) * freq_subday_interval, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time))
            when 4 then dateadd(minute, (Datediff(minute, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time), getdate())/freq_subday_interval + 1) * freq_subday_interval, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time))
            when 2 then dateadd(second, (Datediff(minute, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time), getdate())/freq_subday_interval + 1) * freq_subday_interval, [dbo].[agent_datetime](sjs.next_run_date, sjs.next_run_time))
        end)
FROM msdb.[dbo].[sysschedules] ss
inner join msdb.[dbo].[sysjobschedules] sjs on ss.schedule_id = sjs.schedule_id
inner join msdb.[dbo].[sysjobs] sj on sjs.job_id = sj.job_id
where sj.Name = @jobname  and ss.enabled = 1