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?
SELECT j.[name] as job_name,
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