log_reuse_wait_desc

There are database files (.mdf) and log files (.ldf). Both may grow. Database files grow if there is more data being stored in them. they grow when full either by a fixed percentage, or by a constant amount. When they grow, there will a temporary period during which the database will not respond well, since the .mdf file is being heavily used during growth. This same thing is true of transaction log files. Immediately after growth, there will be unused space in both file types.

The size of the files is like a high-water mark – at some point in the past, the file needed to be that size. In the database, unless data was removed, the file NEEDS to be that large, more data will be added and eventually the file will fill and need to grow again. there are ways to mitigate this, but with more data comes bigger (or more) .mdf files.

This is not true of log files. Logs contain information about transactions. After a transaction completes, either the log file space can be reclaimed (truncated, cleared) or the log data will wait until conditions are right, then the log entry can be discarded. In a full recovery model, data in the translation log might become available to clear following a log backup; in the simple recovery model, following a checkpoint which writes the data, which is in cache, to disk. A growing log file means that either transaction data is coming in faster than can be ‘handled’ or the condition to allow reclamation has not recently occurred, or occured yet.

In a steady-state condition, the log file will grow large enough, and then log records can be removed (truncated, cleared) and new records recreated without the need to grow the log, or the container. To make the container smaller arbitrarily, would require the file growth in the future, with temporary but possibly significant performance impact. Ideally, the log file will never growth an when the database is setup, the log will be ‘pregrown” to the correct size.

Some “usual” conditions can cause the log file to accumulate an “unusual” number of log entries. Data deletion, bulk imports, unusual use of the tempdb, and such will log events and traction data rapidly. If we know these types of events have occurred, we MIGHT consider that they will not happen again and that we can safely shrink the logs to a “normal” level. we should do this, rather than shrink the log as much as possible.

Possible descriptions are
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA

If the log files are increase, the query:

select @@servername, name, log_reuse_wait_desc from sys.databases

will tell us why the log was not able to be reduced the last time that happened. This can give us a clue as to what might be running so that the shrink did not occur as expected. if the log is not growing, the excepted value for log)resuse_wait_desc would be “nothing”, although this can be seen at anytime if the log is growing and there is nothing special that caused it not to be cleared.

 

Paul Randal, SQLSkills article.  2009 TechNet Article on Logging by Paul Randal. 

Advertisements