Suppressing SQL Server Alerts to Reduce Emails

By: K. Brian Kelley   |   Updated: 2016-02-17   |   Comments (2)   |   Related: > SQL Server Agent

Problem

We have SQL Server alerts configured and they notify us when there are issues. However, one problem we have is that sometimes we get a lot of email all at once because a particular alert fires over and over again. For instance, we have an alert for failed SQL Server logins. When our security team runs their network scans, which includes login attempts against each of our SQL Servers, we get a bunch of emails. How can we reduce the amount of emails while still keeping our alerts?

Solution

Likely you have an alert configured to fire against a message ID of 18456, the message ID for failed logins:

And you have it configured to email the DBAs when that alert fires:

And likely you have it configured to return the details of the alert via the Options tab:

But if you note, I've boxed in a section that often isn't configured, which is the Delay between responses. This is how you can suppress the amount of alerts you get for a given event. What this setting tells SQL Server is how long to pause before sending another notification for the same alert. So it doesn't tell SQL Server how many messages it can send, but how often it's allowed to send messages. This indirectly can limit the number of messages sent.

The way this setting works is once SQL Server sends a response due to an alert, it will not send another response until the delay time has elapsed. Therefore, in situations like what was given in the problem, where the security team is running a network scan, their login attempts will likely be all together in a short period of time. The alert will fire indicating someone had a failed login, which can then be tied back to what the security team was doing. However, if you have the situation of a misconfigured application or someone actually trying to hack into the SQL Server, you'll keep getting periodic emails, indicating that something is going on.

Therefore, let's change the setting to something reasonable, such as a delay every 5 minutes:

And if you have scripts which build the alerts, such as when standing up a new SQL Server, you'll need to add the parameter @delay_between_responses, which is configured in seconds. Therefore, for five minutes, we'll configure the alert for 300:

EXEC msdb.dbo.sp_add_alert @name=N'Failed Login', 
		@message_id=18456, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1;
GO

Configured on each SQL Server, this should reduce the total amount of messages per SQL Server. Unfortunately, without using some sort of third party management software, we can't reduce the amount of messages across the board. Therefore, if your security group is scanning, you'll see an email per SQL Server, not just the initial alert across your entire farm.

Next Steps

• See how to configure critical error alerts for SQL Server.
• Notify when violations of Policy Based Management happen.
• Learn how to set up SQL Mail for notifications.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips