The Log Flush Wait Time alarm becomes active when the duration of the last log flush for a database exceeds a threshold.
Because users make modifications to SQL Server databases, SQL Server records these changes in a memory structure called the Log Cache. Each SQL Server database has its own log cache.
When a user transaction is committed (either explicitly, by means of a COMMIT statement, or implicitly), SQL Server writes all changes from the Log Cache out to the log files on disk. This process is called a log flush. The user that issued the commit must wait until the log flush is complete before they can continue. If the log flush takes a long time, this degrades the user's response time.
Foglight for SQL Server checks the log flush wait time for the last log flush performed for each database. If a database has a slow log flush, and then has no update activity (and therefore no more log flushes) for a long time, Foglight for SQL Server continues to report this as an alarm until another log flush is performed for that database.
To handle this alarm:
- On the Databases drilldown, select the Summary panel to review the Log Flush Wait Time counter in the Database History graph. The database with the high graph values is the one experiencing the problem. If a database has a consistently high value that never changes, run SQL command CHECKPOINT on that database to force another log flush and check the value in Foglight for SQL Server again.
- Select the Transaction Logs panel on the Databases drilldown to find the disks on which the log for this database resides.
- Consider moving the log files to disks that support fast write activity (for example, a fast RAID controller with write-back caching enabled).
- Consider moving log files off RAID-5 devices as these are optimized for read activity, and log files generate mainly write activity.