Why do I have to backup a transaction log twice for it to shrink?

Question

This question was attempted twice on experts-exchange with no real answer.

I know that I should not shrink the log file – that is not the question.

If you feel like answering that one should not do it- that has been confirmed many times yes and it is not the goal of this question.

Setting to Simple backup is also not an option.

It has been bugging me for years – the fact that I do now know why it should be backed up twice.

I recently saw the first log file that was able to shrink every time on the first attempt – it was unbelievable. I asked the guy about it and all I could make out was “reorganize before releasing” – I do not know what he was referring to. I am not able to contact him again.

I know this from the existing posts on E-E:
“Shrinking removes inactive parts of the log. In order to be inactive, the log must be truncated which occurs as part of log backup.

However, transaction log is made up of many virtual log files that are used in a round robin fashion. Only virtual logs at the end of the file can be pruned so if SQL server is currently using the last virtual log, nothing can be removed. The old fix in sql 7 was to run a script that ran enough dummy transactions to fill up the log and wrap the pointer around to the first virtual log (you can still use this method if you prefer).
http://support.microsoft.com/kb/256650/EN-US

DBCC now does this for you but still requires the extra step of truncating the log again so the inactive space can be deleted”

From another user:
“All I had to do was discontinue the automated optimization and integrity checks the last Admin had left on the database maintenance plans.”

asked 2017-04-07 by Peter PitLock

Answer

SQL Server’s transaction log files are made up of Virtual Log Files (VLFs). When you shrink a log file, it will only release unused VLFs at the end of the file. There is always going to be at least one VLF that is being used for current activity.

Consider this simplified example. You have a Log file that is made up of 6 equally sized VLFs. For simplicity, let’s say 1 VLF = 1MB, so the total log file is 6MB. You want to shrink it to 3MB. You perform a transaction log backup, which makes some VLF (we’ll pick 4 at random) active, and the rest available for reuse:

1____2____3____4_XX_5____6____

Now you perform a shrink with a target size of 3MB. Shrink can only lop off the unused VLFs at the end, but cannot go all the way down to the target size of 3MB:

1____2____3____4_XX_

You can’t shrink any further, because VLF 4 is in use, and that’s just how things work. You need VLF 4 to be available for reuse before it can be truncated. So you take a second transaction log backup–VLF 4 gets backed up, and VLF 1 becomes the active VLF: