How to monitor transaction log full

• 
  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
  
  

  Alex B December 20, 2018 14:09

  - 0 +
• 
  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.

  Kerstin December 20, 2018 14:18

  - 0 +
• 
  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

  Alex B December 20, 2018 18:34

  - 0 +
• 
  Ok thanks, I have already voted. For us this is a very important improvement.
  // Kerstin

  Kerstin December 20, 2018 21:25

  - 0 +
• 

  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.
  

  Russell D January 02, 2019 08:51

  - 0 +
• 
  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

  Kerstin January 02, 2019 09:12

  - 0 +
• 
  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.
  

  Russell D January 02, 2019 09:55

  - 0 +
• 
  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?

  endix March 27, 2023 17:42

  - 0 +
• 
  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

  Alex B March 28, 2023 12:15

  - 0 +

Add comment

Please sign in to leave a comment.