Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 485, Visits: 848
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
, j.name
, ''
, 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)
union
select cast(h2.instance_id as int)
, j.name
, 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, j.name, 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse