No Alarm Triggered When SQL Server Transaction Log Filled Up (4246880)

A SQL Server agent did not trigger an alarm when the transaction log became full due to an active transaction.

Foglight relies on scheduled data collection intervals to detect and alert on log file usage. If a transaction log fills and resolves itself between two collection cycles, the event may not be captured, and no alarm will be triggered. This is especially likely in environments with high transaction volume and default collection intervals (e.g., 4 hours).

To improve visibility and reduce the chance of missing critical log full events, consider the following options:

Option A: SQL Server Error Log Filtering

• Configure Foglight to monitor the SQL Server Error Log for:
  • Severity 17 (this is included in the agent by default)
  • Error 9002 (Transaction log full)
• Ensure that critical severity filtering is enabled so these events trigger alarms.
• Advantage: More frequent detection without increasing Foglight’s query load.

Option B: Adjust Collection Frequency

• Reduce the collection interval for the File Groups metric (e.g., from 4 hours to 1 hour).
• Advantage: Increases chances of capturing transient issues.
• Limitation: Events may still be missed if they occur and resolve between intervals.

Option C: Lower Thresholds on the existing "DBSS - Log Files Utilization" Rule

• Adjust the threshold for the existing transaction log usage rule (e.g., from 90% to 80%).
• Advantage: More proactive alerting.
• Limitation: This might still miss events if log usage spikes and resolves quickly between collections.

• Customers concerned about performance impact from more frequent Foglight queries should test shorter intervals during low-traffic periods.
• SQL Server Agent alerts for Error 9002 can be used as a lightweight, native alternative to Foglight-based detection.