SQL SERVER – WRITELOG – Wait Type – Day 17 of 28

WRITELOG is one of the most interesting wait types. So far we have seen a lot of different wait types, but this log type is associated with log file which makes it interesting to deal with.

From Book On-Line:

WRITELOG
Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

WRITELOG Explanation:

This wait type is usually seen in the heavy transactional database. When data is modified, it is written both on the log cache and buffer cache. This wait type occurs when data in the log cache is flushing to the disk. During this time, the session has to wait due to WRITELOG.

I have recently seen this wait type’s persistence at my client’s place, where one of the long-running transactions was stopped by the user causing it to roll back. In the future, I will see if I could re-create this situation once again on my machine to validate the relation.

Reducing WRITELOG wait:

There are several suggestions to reduce this wait stats:

  • Move Transaction Log to Separate Disk from mdf and other files.
  • Avoid cursor-like coding methodology and frequent committing of statements.
  • Find the most active file based on IO stall time based on the script written over here.
  • You can also use fn_virtualfilestats to find IO-related issues using the script mentioned over here.
  • Check the IO-related counters (PhysicalDisk:Avg.Disk Queue Length, PhysicalDisk:Disk Read Bytes/sec and PhysicalDisk :Disk Write Bytes/sec) for additional details. Read about them over here.

There are two excellent resources by Paul Randal, I suggest you understand the subject from those videos. The links to videos are here and here.

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – WRITELOG – Wait Type – Day 17 of 28

  1. Hi Dave,

    Can you please fix the link for “Find the most active file based on IO stall time based on the script written over”. It appears to be broken.

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s