We have had a power failure and servers were down for about 28 hours. I now need to find which jobs did not run during that time without having to check each job individually. How can I easily list jobs that were scheduled to run but did not run during a period in the past?
Why not use the Job Activity Monitor in SQL Server Agent? It is a GUI interface showing all jobs on your system. It has columns of Job Name, Enabled, Last Run Outcome, Next Run (date). You could also use a script similar to this. SELECT sj.[name] AS 'Job Name' , CASE WHEN sjh.run_status = 0 THEN 'Failed' WHEN sjh.run_status = 1 THEN 'Succeeded' WHEN sjh.run_status = 2 THEN 'Retry (step only)' WHEN sjh.run_status = 3 THEN 'Canceled' WHEN sjh.run_status = 4 THEN 'In-progress message' WHEN sjh.run_status = 5 THEN 'Unknown' END AS 'Job Status' , MAX(sjh.run_date) AS 'Date Last Run' FROM [msdb].[dbo].[sysjobhistory] sjh JOIN [msdb].[dbo].[sysjobs] sj ON sj.job_id = sjh.job_id --WHERE sjh.run_status = 0 GROUP BY sj.[name], sjh.run_status