How to monitor transaction log full
Kerstin
Posts: 22 Bronze 1
How do you suggest we monitor this error? The errorlog cannot be filtered on errornumber and severity and this is a serious error.
Error: 9002, Severity: 17, State: 4.
The transaction log for database 'XXX_Staging' is full due to 'ACTIVE_TRANSACTION'.
Could not write a checkpoint record in database XXX_Staging because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
Error: 9002, Severity: 17, State: 4.
The transaction log for database 'XXX_Staging' is full due to 'ACTIVE_TRANSACTION'.
Could not write a checkpoint record in database XXX_Staging because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
Tagged:
Best Answer
-
Alex B
Posts: 1,157 Diamond 4
Hi @Kerstin,
Apologies, I mistook filter in your sentence for what I described above. This is functionality that we have had several requests on and the team are researching improvements to the alert configuration (see the roadmap). I believe this is something the team are aware of and will hopefully be included in that work.
In the meantime you should vote on this Uservoice suggestion:
https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/32281798-exclude-specific-alerts-from-sql-server-error-log related to your request.
Kind regards,
Alex
Answers
The SQL Server Error Log Entry alert is customizable by severity and it defaults to greater than or equal to 20 (severity):
You can either reduce this one level to 17, or use multiple thresholds and have the low severity be at 17 (or whatever you deem appropriate). You just need to go to Configuration > Alert settings and then choose this alert for the appropriate level of the hierarchy (all servers or a specific machine/instance if that is what you require).
I hope this helps!
Kind regards,
Alex
Have you visited our Help Center?
The problem with this is that we are flooded with entries like
Error: 17806, Severity: 20, State: 14. SSPI handshake failed
and other stuff that we are not really interested in and need to filter out some of the alerts or error codes. One solution would be a text filter or
to be able to set alarms on the errorcode rather than on the severity.
// Kerstin
https://documentation.red-gate.com/sm8/troubleshooting/error-messages/sql-monitor-connection-error-cannot-generate-sspi-context has some help on this. It's not a huge problem I believe, but its worth digging into.
That is one of the options, yes, and possibly the best.
The other would be as described above with the "SQL Server error log entry" and adding regular expressions, which are now an option, to exclude any other entries that are raised (like the SSPI error was above, which could now be excluded).
Kind regards,
Alex
Have you visited our Help Center?