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 :
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.