4

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."

Improve this question
5
  • So the question is how to effectively get truncation of a logfile which has grown beyond measure? --> Take a logbackup (if you have a full backup first) then perform a checkpoint, then do shrinkfile with truncate_only, then grow the file to a more appropriate size.
    – Stijn Wynants
    Apr 7, 2017 at 7:47
  • Thanks stijn, I have not done it that way. I've made a backup of log , shrinked log, made new backup of log , shrinked log. I've seen this behavior of SQL everywhere. I have seen once that one can do it with one iteration. What "fix" or change does one do for this? I will look at the way you mention too
    – Peter PitLock
    Apr 7, 2017 at 9:20
  • 1
    It will always depend on how much traffic is on your database. If there is no traffic, you will be able to do this instantly, if there is traffic there is a good change that your transactionlog file is in use, this way you will have to repeat the process a few times to get it to shrink.
    – Stijn Wynants
    Apr 7, 2017 at 9:22
  • Thanks Stijn, have you had this issue as well before (repeating the process twice)?
    – Peter PitLock
    Apr 7, 2017 at 10:29
  • yes, and that is due to the activity inside your database. If a transaction is running during your execution, you will get the "error" end of file is in use
    – Stijn Wynants
    Apr 7, 2017 at 11:57

1 Answer 1

Reset to default
14

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:

1_XX_2____3____4____

Now, you can perform a second shrink, and get the transaction log down to the target size of 3MB:

1_XX_2____3____
Improve this answer
3
  • 1
    This is true for Simple mode databases as well, although the "log backup" steps aren't necessary since completed transactions will clear on their own. Shrink once, wait for a while for existing transactions to complete and the "active" area to roll back around, then shrink again.
    – BradC
    Apr 7, 2017 at 18:28
  • @BradC Correct -- The distinction being that if there is zero activity in Simple Recovery, it's probably a 1-step shrink, without having to beat on it multiple times. In Full recovery, you're very likely to have to do a bit of dancing to shrink the log. (Presumably so you can re-grow it to the ideal size!)
    – AMtwo
    Apr 7, 2017 at 18:32
  • 1
    It is needed for databases in simple recovery if the active VLF is near the end of the log file. The difference is you need to do 2 checkpoint operations back-to-back instead of log backups. To be clear, you don't have to do backup -> shrink -> backup -> shrink. Just two log backups back to back and it will trigger the active VLF to move to the start of the file. This will not work 100% of the time. If the active VLF has an active transaction in it, it can't move the active VLF to the beginning of the log file. There can't be any actively running transactions for this to work.
    – Robert L Davis
    Apr 7, 2017 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.