CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2sql-job
Martin
Martin

Reputation: 40593

Detect jobs that haven't run as scheduled

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!

Upvotes: 7

Views: 3785

Answers (3)

Tdubs
Tdubs

Reputation: 29

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.

Upvotes: 0

SheldonCooper
SheldonCooper

Reputation: 103

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

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can try undocumented procedure

sys.xp_sqlagent_enum_jobs

This procedure returns job last run date and time.

Upvotes: 1

Related Questions