WriteLog wait time

During a transaction, data is written to the log cache so that it’s ready to be written to the log file on commit, or can be rolled back if necessary. When the log cache is being flushed to disk, the SQL Server session will wait on the WriteLog wait type.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

During a transaction, data is written to the log cache so that it’s ready to be written to the log file on commit, or can be rolled back if necessary. When the log cache is being flushed to disk, the SQL Server session will wait on the WriteLog wait type. If this happens all the time, it may suggest disk bottlenecks where the transaction log is stored.

For more information, see http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/17/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server.aspx.

Metric definition

Name

WriteLog Wait Time (ms per sec)

Description

Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. Metric shows the wait time (in ms) per elapsed second. For more information, see http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/17/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server.aspx.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

0

Collection frequency

60

Use collected or calculated values

Check the Use a calculated rate of change between collections check box

Metric collection

Enabled

Alert definition

Alert name

WriteLog wait time too high

Description

The WriteLog wait time is too high. This can occur while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

Raise an alert when the metric value goes

above the defined threshholds

Default threshold values

High:800
Medium:500
Low:100

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

3 collections

Alert is

Enabled