Full Transaction Log Resulted in 9919 Alerts

linque

Set up your monitoring carefully!

Between 11:20 AM this morning and 12:15 PM (just under an hour) I received 9,919 alerts from one database server. The transaction log on one of our production databases grew to it's max size of 75GB today. Someone kicked off something in the application that caused a huge amount of activity on the database. Anyway, the monitored event was "SQL Server error log entry", and spit out 9,919 of these:

Error: 9002, Severity: 17, State: 2.
The transaction log for database 'My_Database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Here are my settings for the alert:

Description

This alert will be raised when an error log entry higher than a severity level you specify is written to the SQL Server error log. You can configure the alert so that the alert level depends on the error severity.
Alert thresholds

Raise alert for errors with severity level equal to or greater than:

	
High		20
	
Medium	19	
	
Low		17

Fortunately, I only received about 1000 of these alerts through email due to this:

SQL Monitor has exceeded the maximum number of emails for a 24 hr period


This is an information message from SQL Monitor 
SQL Monitor has sent 1000 alert notification emails in the last 24 hours. To prevent overloading your mail server, SQL Monitor by default will not send more than 1000 emails during any 24 hour period.
Note: Alerts will continue to be raised, and can be viewed in SQL Monitor. 
What should I do now? 
You should first investigate why so many alerts are being raised. If the number of raised alerts is higher than expected, check what types of alerts are being raised most frequently and consider adjusting their thresholds. For example, try increasing the duration for alerts such as long running query or processor utilization, or turn off alerts for less important servers or databases.
You could also change the Notification settings for your alerts so that emails are not sent for certain types of alert or for particular monitored objects.
In SQL Monitor open one of the alerts that is being raised repeatedly, and click Configure alert to change its settings.
If you want to change the maximum number of emails sent per day by SQL Monitor, do the following:
1.	Open the RedGate.Response.Engine.Alerting.Base.Service.exe.settings.config file 
(This file is located by default in C:\ProgramData\Red Gate\SQL Monitor 2) 
2.	Edit the value of <alertSettings maxEmails="1000" /> 

I have two questions.

1 - Is there a way to suppress duplicate error messages that are emailed? You can imagine my panic when I looked at my iPhone and saw 1000+ unread messages coming from SQL Monitor.

2 - Is there a way to monitor log file usage? For example, if the log file is approaching 75% of it's allowed space, or if it hits 50 GB send an alert.

I would love to have caught this before I left for lunch.

Chris Spencer

Hi

In answer to your questions:
1. There is no way at the moment of suppressing duplicates like this. Although the error messages are identical, our software sees each error log entry as a new event that requires alerting.

2. I've raised an enhancement request reference SRP-3078 for this. It sounds like it could be a useful alert. The information on the log size is available in the SQL Monitor data repository so it may be possible for users to create their own alerts based on values in the database in the meantime. The relevant data can be found in Cluster_SqlServer_Database_Storage_StableSamples_View and Cluster_SqlServer_Database_File_StableSamples_View

Regards
Chris

linque

Chris Spencer wrote:

...our software sees each error log entry as a new event that requires alerting.

Yeah, I figured that out when I opened up the SQL Server error log. Hopefully, there will be a way to suppress this in the future.

Chris Spencer wrote:

The information on the log size is available in the SQL Monitor data repository so it may be possible for users to create their own alerts based on values in the database in the meantime.

Are you saying it's possible to create custom alerts?

Thank you for adding the feature request, and for responding quickly.

Chris Spencer

linque wrote:

Are you saying it's possible to create custom alerts?

No this is not supported in SQL Monitor at the moment. I was thinking yesterday that as the data was available in our database it would be possible to use native SQL Server alerting to periodically check this data and send an email. However, after reading up on how SQL Server alerting works I don't think this is possible.

Regards
Chris