Suppressing SQL Server Alerts to Reduce Emails

By:   |   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:

Alert Definition Configuration

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

Alert Response Configuration

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

Alert Options - No Alert Suppression

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:

Configure Alert Suppression

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley 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



Comments For This Article




Monday, August 13, 2018 - 10:10:37 AM - Rajasekhar Reddy Back To Top (77160)

 

 For first mail also. it delayed 5 minutes. Then its not good advise


Monday, February 22, 2016 - 11:55:46 AM - Bobznkazoo Back To Top (40745)

Excellent tip!

...and great timing.  We got 3700 emails during a 3 hour time period from a 017 alert on a server this weekend.















get free sql tips
agree to terms