I use SQL Server agent notifications a lot for notifying of failed jobs. Usually, jobs run every hour so I don't mind getting a few emails if the job fails.
However, I want to run a job every minute now and leave it running. I really don't want to get 100s of emails if it fails for a few hours.
Can I set a limit on the number of emails SQL Server agent will send for a job?
This feature is already built into SQL Server. I had a similar situation and what I did was to change my code to use a custom error number, above 50000, and set up a new Alert, under SQL Server Agent|Alerts in Management Studio, with that custom error number. Under the Options section, there is a Delay between responses section that can be used to mitigate the volume of notifications per hour. I had my process monitoring every 10 seconds, but notifying every 5 minutes because I wanted to know when the critical situation started, but not be overwhelmed with alerts.
I don't think there's an option available to limit the number of times that a job can report failure.
What comes to mind is to handle the notification yourself. Don't have SQL Server send an email on job failure, make that email part of your job script. You can log the email activity and control when your job sends them.
It's pretty easy if your job is a single step, just add a second step and on failure of the first job the On Failure Action would be Go To The Next Step where you'll send (or not) the email. If your first step succeeds, Quit The Job Reporting Success.
If you have multiple steps and need to notify of failure if any one of them fails, you'll need to add that error checking and notification routine to the steps themselves.
High Level overview of what you can do..
1.Turn off email notifications for the job
2.Log every failure to table
3.Run one more job at your desired frequency which shows summarized data of the job status
One more Approach would work,if your mail subject contains some thing to be identified as unique like 'Job test Failed' or 'job test succeded'..
Then you can query sysmail_allitems like below
Pseduo logic:
IF (SELECT COUNT(*) from sysmail_allitems where subject like 'Job test%'
and sent_status='sent' and cast(sent_date as date)=cast(getdate() as date))=2
Begin
Return--there exists a record so dont send any emails
END
