Find failed SQL Jobs

• free_mascot

  One Orange Chip

  Points: 27164

  More actions

  February 20, 2009 at 1:19 am

  #201467

  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

  SSCrazy

  Points: 2636

  More actions

  March 17, 2009 at 7:01 am

  #960985

  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.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 2, 2016 at 4:15 pm

  #1876107

  Thanks for the script.