Get failed job per hour

Transact-SQL
1

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

2

Change this:

to this:

and dateadd(second
           ,(h.run_duration/10000)*60*60
           +((h.run_duration/100)%100)*60
           +h.run_duration%100
           ,msdb.dbo.agent_datetime(h.run_date,h.run_time)
           )
  >=dateadd(hour,-1,current_timestamp)
3

Hi,
it's not do what i need.
if the job runs lets say for 3 hours and failed in the end the job will report failed in it start time. there is no end time in the sql server agent failed jobs so when i run the query every hour to find that failed jobs i miss the job that failed 3 hours ago.

4

Did you try it?

That's why I calculate the end time, by adding the duration time:

5

i did try this and it is not working.
what if the job runs for 3-4-7-9 hours then failed in the end..
it wan't capture it if i run the job that check for failed jobs every hour for hour ago

6

Since I don't have your data to test on, please find a couple of failed jobs that meets your requirements (failed within the last hour, and did run for more than 1 hour), and that your query doesn't find, and insert the instance_id in xxx, yyy, zzz of the below query, and post the result:

select instance_id,[message],run_status,run_date,run_time,run_duration,current_timestamp
  from msdb.dbo.sysjobhistory
 where instance_id in (xxx,yyy,zzz)