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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy