SQL Server: How to Limit Number of Emails Sent by SQL Server Agent

jobssql serversql server 2014sql-server-agent

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?

Best Answer

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.

Related Solutions

Sql-server – Renaming SQL Server Agent operator

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance:

exec msdb.dbo.sp_update_operator 
    @name = 'OldOperatorName', 
    @new_name = 'NewOperatorName';
go

As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing. It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story? Don't rely on a UI. If you want to have full control over what's happening then you should be using T-SQL. There will be no question in your mind if you're writing out the code to do what you want. No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name. So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.

SQL Server Job Agent – How to Handle Gaps in Job History

Check the duration of the job prior to the first slot it "missed." Typically job history won't have gaps unless:

The previous instance of the job is still running - SQL Server knows not to start two copies of the same job concurrently, so it just skips that slot.
The job was disabled during that time.
Someone deleted the history from msdb.dbo.sysjobhistory.

Best Answer

Related Solutions

Sql-server – Renaming SQL Server Agent operator

SQL Server Job Agent – How to Handle Gaps in Job History

Related Question