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]
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.
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