Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Find failed SQL Jobs Expand / Collapse
Posted Friday, February 20, 2009 1:19 AM


Group: General Forum Members
Last Login: Thursday, March 17, 2016 5:13 AM
Points: 2,796, Visits: 2,232
Comments posted to this topic are about the item Find failed SQL Jobs

"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #661104
Posted Tuesday, March 17, 2009 7:01 AM


Group: General Forum Members
Last Login: Wednesday, June 22, 2016 5:43 AM
Points: 492, Visits: 1,008
I started down that line but found that didn't give me everything. I now have stored procedures for
1) Identifying if a backup job exists on a database - we have a convention that every database has an associated backup device so the logic should be a job is created to perform a backup.
2) Identifying failed jobs and also jobs that are either disabled or have never been run
The one thing I haven't checked is that a backup has been run on a database "today" because I am assuming that if a job is defined then an appropriate schedule is set for the job (it's a small community creating backup jobs so fairly safe here!)
So for the 1st:
select	[name]
from sys.sysdatabases
where [name] not in (
select database_name
from msdb..backupset
and [name] not in ('tempdb','model')

and the 2nd
select	null
, ''
, null
, null
, case
when j.enabled != 1 then 'Disabled'
else 'Never Run'
end as "Run Status"
, null
, null
, null
, @@servername
, j.job_id
from msdb..sysjobs j
where (
j.job_id not in (
SELECT h.job_id
FROM [msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id )
or (j.enabled != 1)
select cast(h2.instance_id as int)
, h2.step_name
, h2.step_id
, h2.[message]
, case
when h2.run_status = 0 then 'Failed'
when h2.run_status = 3 then 'Cancelled'
else 'Not listed'
end as "Run Status"
, h2.run_date
, h2.run_time
, h2.operator_id_emailed as "Operator Emailed"
, h2.server
, h2.job_id
from msdb..sysjobhistory h2
join msdb..sysjobs j on j.job_id = h2.job_id
where h2.instance_id in (
SELECT max(h.instance_id)
FROM [msdb].[dbo].[sysjobhistory] h
group by h.[job_id]
and h2.run_status in (0,2)
order by 7,8,, 3

I doubt it's the neatest or most efficient code but it's pretty simple and ties in with a string of other monitoring routines we use to summarise the state of play first thing in the morning quickly & easily.

Hope that might help other people a little.
Post #677402
Posted Monday, May 2, 2016 4:15 PM


Group: General Forum Members
Last Login: Wednesday, June 22, 2016 6:21 AM
Points: 7,841, Visits: 755
Thanks for the script.
Post #1782743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse