How to identify SQL Agent jobs missed during server downtime

Category : Blogs Published : February 17, 2014 User Rating : 5 Stars      Views : 2.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
Any SQL agent jobs scheduled to run during a period of server downtime will not be run. For some jobs, such as an hourly transaction log backup job, this is not a particular problem because the backup job will be run at the next scheduled time once the server has started up again, and SQL Server will just catch up. However for other jobs, such as a daily report or data extract it is likely to cause more of an issue.

I like to create a list of jobs (and their scheduled run times) that will probably be missed while a server is down. Once the server is back up I then work out which jobs were actually missed (which depends on the amount of downtime) and run any important ones manually. This does mean a certain amount of planning ahead in that the list of jobs needs to be created before the server is taken down.

SQL Script

The following script will create a list of all jobs that are scheduled to be run at some point in the future :
SELECT     J.name
          ,next_scheduled_run_date AS [Next Scheduled Run Time]
          ,start_execution_date    AS [Last Run Time]
          ,DATEDIFF(mi,start_execution_date,next_scheduled_run_date)
                                   AS [Interval between Job Runs in Minutes]
FROM       msdb.dbo.sysjobactivity JA
JOIN       msdb.dbo.sysjobs J
ON         JA.job_id = J.job_id
WHERE      next_scheduled_run_date >= GetDate()
ORDER BY   [Next Scheduled Run Time]
For each job the next scheduled run time and the last time run are listed, as well as the time between the last and next run in minutes. An example result is below : How to identify SQL Agent jobs that are missed during server downtime

These results should enable you to work out which jobs have been missed and need to be run manually. Note that the "Interval between Job Runs in Minutes" figure gives an indication of how frequently the jobs run, however this is calculated by comparing the last and next run time, so it won’t tell you if the job is run at irregular intervals.

One final point, the sysjobhistory table used in this query is updated instantly, unlike some similar tables and views in the msdb database that only get updated periodically, so the information should be up to date.



Link back to this article : https://www.sqlmatters.com/Articles/How to identify SQL Agent jobs that are missed during server downtime.aspx

Keywords

SQL, blogs, agent, jobs, downtime


Comments
Post by Fernando on Fri 09 Jan 2015 12:02. Report Inappropriate Post

Thank you. Is a very good idea
Post by Bill on Sun 22 May 2022 10:12. Report Inappropriate Post

sysjobhistory? where? Are you talking about sysjobactivity

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered