Only send email when a JOB fails at a given amount of times.

  • wlblee38

    Old Hand

    Points: 326

    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.

  • Sue_H

    SSC Guru

    Points: 89874

    wlblee38 - Wednesday, March 6, 2019 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.

    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

  • wlblee38

    Old Hand

    Points: 326

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply