How to monitor transaction log full

KerstinKerstin Posts: 22 Bronze 1
in Redgate Monitor
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.
Tagged:
· Share on Twitter

Best Answer

Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @Kerstin,

    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

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • KerstinKerstin Posts: 22 Bronze 1
    Thank you Alex.
    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.
    · Share on Twitter
  • KerstinKerstin Posts: 22 Bronze 1
    Ok thanks, I have already voted. For us this is a very important improvement.
    // Kerstin
    · Share on Twitter
  • Russell DRussell D Posts: 1,324 Diamond 5
    Kerstin said:
    Thank you Alex.
    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.
    It might be worth trying to find the cause of the SSPI error.

    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.
    Have you visited our Help Centre?
    · Share on Twitter
  • KerstinKerstin Posts: 22 Bronze 1
    Thanks Russell, Redgate is not causing the SSPI errors.  In one case there is an application with AD authenticated users that shows this error when a user's account is locked and we also get a lot of errors in the error log when there is a security scan which is done regurarly. I would like to have the ability to filter errors out or to trigger different alarms on different severity/error numbers
    · Share on Twitter
  • Russell DRussell D Posts: 1,324 Diamond 5
    That makes sense then - I agree we need to do something about it, the simplest option is to add regex filtering to all alerts, unfortunately its a lot of work.

    I'll bring it up in sprint planning later today. It's something we definitely want to do but it isn't trivial.
    Have you visited our Help Centre?
    · Share on Twitter
  • endixendix Posts: 12 Bronze 1
    Apologies for reviving the old thread, but how exactly does one get alerted about "transaction log full"? Seems the implied answer is to enable "SQL Server log entry" alert with a regex for "The transaction log for database ... is full". Am I right?
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @endix,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.