Sql Server - Tsql To Detect Missed Agent Jobs Due To Overrun

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 enab

Solution 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 
            when8then 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))
            when4then 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))
            when2then 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
innerjoin msdb.[dbo].[sysjobschedules] sjs on ss.schedule_id = sjs.schedule_id
innerjoin msdb.[dbo].[sysjobs] sj on sjs.job_id = sj.job_id
where sj.Name =@jobnameand ss.enabled =1