Post reply

SQL Agent job to only mail when having failed more than X times in a timeperiod

  • July 25, 2014 at 5:34 am

    #309451

    Hello

    I have an sql agent job, lets call it A, that runs every 1 min, this job sometimes fails, which is perfectly fine because the NeXT run will pick up any slack from the previous failture.

    I have the job setup to mail me everytime it fails, but this is not very desirable. I don't really mind it failing intermittently, what I am concerned about is if this job failes say 10 times in 10 minutes, because then it is something I need to investigate.

    Can anyone think of a way to do this?

    Other than setting up another job, lets call it B, to keep tabs on job A, and mail me whenever failture number is higher than threshold?

    I could do it so job A, writes to a table everytime it completes, and then have job B check against the table to see if it succeeded, but this seems like a poor way of managing this issue.

    Any ideas?

  • July 25, 2014 at 5:40 am

    #1732317

    tommy 28778 (7/25/2014)

    I could do it so job A, writes to a table everytime it completes, and then have job B check against the table to see if it succeeded, but this seems like a poor way of managing this issue.

    You DO need to setup an additional job to check/count the number of failed job-runs. But you do NOT need to write the results of the initial job to a table. You can just let the additional job query the [msdb]..[sysjobhistory] table and filter on job_id, run_date, run_time and run_status.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 25, 2014 at 6:18 am

    #1732325

    Doh, I should have thought of just pulling that data.

    Thanks, already got it up and running somewhat.

    I might post the code once I am done.

Viewing 3 posts - 1 through 2 (of 2 total)

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