SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find failed SQL Jobs


Find failed SQL Jobs

Author
Message
free_mascot
free_mascot
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3973 Visits: 2235
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."
Balance
Balance
Mr or Mrs. 500
Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)

Group: General Forum Members
Points: 578 Visits: 1048
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.
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13762 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search