Investigate SQL Server WRITELOG Waits

Why this problem matters

WRITELOG indicates waiting for log records to be hardened to stable storage. Elevated waits can stem from log-storage latency, very frequent commits, or high log-generation volume.

How to diagnose it

Inspect write count and cumulative write stall for log files by database. Correlate the result with transaction rate, commit pattern, and wait deltas from the same interval.

The query reads cumulative write counters for log files and changes no setting. Sound interpretation requires deltas between two samples and transaction volume for the same period.

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.name AS log_file_name,
       vfs.num_of_writes,
       vfs.io_stall_write_ms,
       CASE WHEN vfs.num_of_writes = 0 THEN NULL
            ELSE 1.0 * vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_stall_ms,
       vfs.num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
WHERE mf.type_desc = 'LOG'
ORDER BY avg_write_stall_ms DESC;

A safe solution approach

Reducing unnecessary tiny transactions without changing application semantics and validating log storage are possible improvements. Durability options must not be changed without understanding their data-loss implications.

Why continuous monitoring matters

Log latency may become visible only during commit-heavy periods and disappear inside cumulative averages. moon can track short- and long-term trends and route alerts through Slack, PagerDuty, or webhooks.