Suppressing SQL Server Alerts to Reduce Emails


By:   |   Updated: 2016-02-17   |   Comments (2)   |   Related: More > 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


Last Updated: 2016-02-17


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

 

 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

Excellent tip!

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools