Do log records ever move in the transaction log?

Question: Why is it that sometimes when I try to shrink a transaction log file, the shrink fails? Why can’t SQL Server just move log records to the start of the transaction log?

Answer: Firstly, the shrink fails because a log file shrink operation doesn’t move anything (unlike a data file shrink operation). A log file shrink simply removes any unused portions of the transaction log at the end of the transaction log file and releases the disk space for other uses.

Related: Why does my transaction log grow but my log backups don't?

The log management system keeps track of which portions of the log (called Virtual Log Files, or VLFs) are able to be removed in this way – basically any VLFs that do not contain transaction log records that are still required by SQL Server for some reason. You can help this process by allowing the log to ‘clear’ every so often (for instance, by taking a transaction log backup in the FULL recovery mode) as clearing the log marks VLFs as no longer active (so they can be reused, or dropped by a shrink operation). There’s a lot more to this than I have space to explain here, so check out this article that goes into greater depth.

If the shrink operation stops, and the output indicates that it didn’t shrink as much as you wanted, then you will need to allow the log to clear, as I mentioned above, and try the shrink operation again. It may be hard to get the log to shrink on a busy production system, however.

Secondly, log records cannot be moved. A log record has a unique number called a Log Sequence Number, or LSN, that identifies the physical position in the transaction log of the log record. This LSN is used in a variety of different ways, all linked to transaction management and crash recovery. If a log record was ever moved, it’s LSN would no longer be valid and so things like crash recovery would cease to work correctly – leading to corruption. This is why a log file shrink operation will stop when it comes across a VLF that it can’t remove – there’s no way for the contents of the VLF to be moved to an earlier position in the transaction log file.

Lastly, and you didn’t mention why you were doing a log file shrink, shrinking a log file should be a rare operation – usually because the file has grown really large unexpectedly. A regular log file shrink should not be performed, as it’s likely the log will just have to grow again – and whenever the log file has to grow, the newly allocated space has to be zero initialized, causing a pause in logging operations for the database while the zero initialization takes place.

Back in March 2010 on this blog I wrote a long post about how to pick the right size for a transaction log – I suggest you check that out too.

Discuss this Blog Entry 1

on May 29, 2012
To find out what is preventing your log from shrinking, run this command. DECLARE @DatabaseName VARCHAR(50) ; SET @DatabaseName = 'NAMEOFDATABASE' --Add name of database SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases WHERE name = @DatabaseName

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×