Why not reduce the database log file size with shrink?

All we need is an easy explanation of the problem, so here it is.

I use SQL server database.
To reduce the database log file I set the recovery model of the database to simple and then Shrink database but the log file does not get the effect.

What is problem?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Don’t shrink the whole database. It is too imprecise. Always target the individual file. I.e., use DBCC SHRINKFILE.

The reason that the log file doesn’t shrink is that the last Virtual Log File is in use. The ldf file is divided into Virtual Log Files (VLF), and the ldf file can only be shrink from the end of the file, removing un-used VLFs. Apparently you have used VLFs at the end of the file, else the file would have shrunk.

What you do is to empty the log (the CHECKPOINT command if in simple recovery or do a log backup if in full recovery) and then execute the DBCC SHRINKFILE command. You might have to repeat these steps several times until the last desired VLFs has gotten into the unused state and can be removed.

Here’s an article I wrote, with some more details to it. And this is another about why it is a bad idea to shrink files, in general.

Method 2

A transaction log is used to store delta values while the transaction is used to save the previous values ​​before performing the data modifications (INSERT, UPDATE, DELETE…) in case of thses modifications are canceled. The different transactions generate many lines in this file, sequentially, and the different user transactions are interleaved.

Attempting to reduce the size of a transaction log is generally not a good idea and may fail especially if:

  • there is a still alive transaction (not terminated by COMMIT or ROLLBACK) started a long time ago
  • log size is already minimized
  • the log is heavily loaded with many transactions

Generaly, reducing the size of a transaction log is an operation that should only be undertaken in exceptional circumstances, as an example in the case of its size becomes really gigantic. A transaction log having a size of the order of 10 to 20% of the overall size of the database is considered normal.

Indeed, the fact of reducing considerably and too low, the size of the transaction file will inevitably force it to grow, this growth being an operation detrimental to performance…

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply