June 9, 2014 at 1:12 pm
SQL 2008 R2: I have a SQL agent job that runs hourly. It's not a big deal if it fails once ... and it's not a huge surprise.
But it should not fail repeatedly. I'm looking for a way to be alerted if it fails > 1 time in any 3 hour block. Just using SQL Server Agent notification on failure is creating too much noise. Does anyone have anything in their back pocket that monitors a particular job_id in sysjobhistory for repeated failures?
June 9, 2014 at 4:15 pm
You could just create another monitor job that queries job history every three hours and sends an alert.
use msdb
go
SELECT j.[name], h.step_id, h.step_name, h.[message],
h.run_status, h.run_date, h.run_time, h.run_duration
FROM sysjobs j
LEFT JOIN sysjobhistory h on j.job_id = h.job_id
WHERE
j.[name] = 'YOUR JOB NAME' AND
run_status = 0
ORDER BY h.run_date, h.run_time
June 11, 2014 at 9:39 am
Thank you for the response. I used a good chunk of that and built a stored proc around it to alert me when I had more than a single failure in a 3 hour period.
June 11, 2014 at 3:03 pm
🙂 I'm glad you were able to use it. I was in a rush and not able to customize it, but I figured you'd get the idea.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply