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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy