get failed job per hour

• Mad-Dog

  SSCertifiable

  Points: 6117

  More actions

  March 2, 2019 at 1:34 pm

  #416737

  Hi,

  I've this script that runs every hour to get the failed job.
  the problem with this script is when there is a job that runs lets says for 2 hour and it's failed in the end the script will not find this failed job.
  what can i do to get the failed jobs per hour even if the job is run more then an hour and failed?
  THX

  SELECT j.[name] as job_name,
      s.step_name,
      h.run_date,
     h.run_time,
     h.message
     
  FROM  msdb.dbo.sysjobhistory h
     INNER JOIN msdb.dbo.sysjobs j
      ON h.job_id = j.job_id
     INNER JOIN msdb.dbo.sysjobsteps s
      ON j.job_id = s.job_id
      --AND h.step_id = s.step_id
  WHERE  h.run_status in (0,2,3) -- Failure
     AND msdb.dbo.agent_datetime (h.run_date,h.run_time) >= (SELECT DATEADD (MI, -60, GETDATE()))
          order by msdb.dbo.agent_datetime (h.run_date,h.run_time) desc

• Jeff Moden

  SSC Guru

  Points: 1004312

  More actions

  March 4, 2019 at 6:01 am

  #2023422

  It's pretty simple to find such overlapping ranges.  Have a gander at the following link to learn how... just change the point of reference from months to hours.
  http://www.sqlservercentral.com/articles/T-SQL/105968/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)