March 6, 2019 at 4:54 pm
I’m looking into a way to design a script or another job to count how often a job fails and send an email alert after it fails on a certain amount of retries. Let’s say I have it maxed at 5 times only then I get an email. Is there a way in a script to do this from another JOB.
March 7, 2019 at 3:16 pm
wlblee38 - Wednesday, March 6, 2019 4:54 PMI’m looking into a way to design a script or another job to count how often a job fails and send an email alert after it fails on a certain amount of retries. Let’s say I have it maxed at 5 times only then I get an email. Is there a way in a script to do this from another JOB.
That's how the retry logic and notification for jobs works. If you set the retry to 5 and it fails on the fifth attempt, you get the notification. You don't get a notification for the other attempts that failed.
You don't want to use that and want another job instead?
Sue
March 11, 2019 at 8:38 pm
hi; thx for your input. It's checking for another JOB. So this JOB checks for another JOB and determine if it failed a number of times and send email from this JOB.
SELECT count(*) as ct, run_status,[name]
FROM msdb.dbo.sysJobHistory h (NOLOCK)
INNER JOIN msdb.dbo.sysJobs j (NOLOCK)
ON j.job_id=h.job_id
WHERE step_id=1
AND j.name='MyJOB' ---this job run every 10 mins
AND run_status=0
AND CAST(MSDB.dbo.agent_datetime(run_date, run_time) AS DATETIME) > DATEADD(minute, -60, getdate()) ---60 minute span...if notice it failed over 5 times I get the email from this job. Not an email from the job it's checking that failed.
group by run_status,[name]
having count(j.name)=5 --number of times it failed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply