February 20, 2009 at 1:19 am
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."
March 17, 2009 at 7:01 am
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]
fromsys.sysdatabases
where[name] not in (
selectdatabase_name
from msdb..backupset
)
and[name] not in ('tempdb','model')
and the 2nd
selectnull
,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
frommsdb..sysjobs j
where(
j.job_id not in (
SELECTh.job_id
FROM[msdb].[dbo].[sysjobhistory] h
wherej.job_id = h.job_id)
)
or(j.enabled != 1)
union
selectcast(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
frommsdb..sysjobhistory h2
joinmsdb..sysjobs j on j.job_id = h2.job_id
whereh2.instance_id in (
SELECTmax(h.instance_id)
FROM[msdb].[dbo].[sysjobhistory] h
group byh.[job_id]
)
andh2.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.
May 2, 2016 at 4:15 pm
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply