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!

Callender

Answer 1

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

Sama

I really want to know if the Agent has skipped a schedule job. This query will tell me if the job has succeeded or not. – Callender

Answer 2

You can try undocumented procedure

sys.xp_sqlagent_enum_jobs

This procedure returns job last run date and time.

Nanceynanchang

Answer 3

To find out better on which jobs have no hisotory. Try running this query.

 select name  from msdb.dbo.sysjobs  where job_id not in (select job_id from sdb.dbo.sysjobhistory)  and enabled = 1

by default each job should have history when it executes.

Inferno