Detect jobs that haven't run as scheduled

Question

I am trying to write a SQL query which tell me if a SQL job has run or not based on its schedule. For instance, if a job was planned to run at 12:00PM and we are 12:05PM and it still hasn't run, I want to know that. What would be the best approach to detect that?

It happened in the past that we had SQL jobs to run periodically and SQL Server didn't run them. To prevent that, we would like to run a query against SQL Server 2008 R2 and check if the job ran or not and then trigger an alert if it hasn't run.

Thanks for the help!

Answer 1

You can try undocumented procedure

sys.xp_sqlagent_enum_jobs

This procedure returns job last run date and time.

Answer 2

This query will tell you the job run details.

Select hist.run_date,hist.run_status,hist.run_time
from msdb..sysjobs jb 
inner join msdb..sysjobhistory hist
on jb.job_id = hist.job_id
where jb.name = 'MyJob'

As per Sql Books Online http://msdn.microsoft.com/en-us/library/ms174997.aspx

run_status

Status of the job execution:

0 = Failed

1 = Succeeded

2 = Retry

3 = Canceled