Log Reuse Waits Explained: LOG_SCAN

2014-01-21 - General, Log Reuse Wait, Series, Transaction Log

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: LOG_SCAN

LOG_SCAN

This one is shy. You will rarely see one on your servers.

A log_reuse_wait_desc value of LOG_SCAN indicates exactly what it sounds like. For some reason SQL Server is executing a scan of the transaction log. While that scan is going on, log truncation cannot happen, potentially leading to this wait type.

So what will cause a log scan? There are a few situations in which SQL Server has to scan the transaction log. The use of the undocumented fn_dblog function will for example cause a log scan. Another example is a checkpoint operation: During a checkpoint SQL Server does a log scan to synchronize log sequence numbers.

A log scan is usually a very brief operation. Therefore this log reuse wait is very transient. If you encounter this wait on you system it will probably be gone by the next time you look.

Summary

SQL Server will return a log_reuse_wait_desc value of LOG_SCAN if it runs out of virtual log files during an active scan of the transaction log. Those scans are usually short and therefore you should not see this wait type for an extended period of time, if ever.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.

Categories: General, Log Reuse Wait, Series, Transaction Log
Tags: , , , , ,

Trackbacks

  1. […] Is your database log file growing out of bound and reporting LOG_SCAN as reason? Read on to find out what that means and what you can do about it. (Is your #SQLServer transaction log growing with a log reuse wait of LOG_SCAN?  […]