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.
