1

I wonder if the internal methods of growing a file using

ALTER DATABASE testdb 
MODIFY FILE 
( 
        NAME = testdb_Log 
      , SIZE = 40960MB  --40 GB
)

work different then letting the file autogrow to the same size over time?

Background of my question: I face very high (>10K) VLF counts on several Log files of different live DBs with full recovery that are sized around 100 GB.

While testing strategies to get this fixed, I resized a Log file to 16 MB having 4 VLFs and let it grow to 40960 MB then with an AUTOGROW setting of 100 MB. Afterwards I had 20 VLF in the file.

My expectation was to see additional

(40960 MB - 16 MB) / 100 MB * (8 VLF per autogrow) = 3276 VLF in the file.

Instead I now have only 20. Why?

Second part of my question: So far I understood that an inappropriate AUTOGROW setting cause the high VLF counts, that is defenitely the case in our dbs where this setting has not been looked after for a long period of time. All the articles explaining how to handle this situation advice to choose a proper value for AUTOGROW. But if I am right assuming that if the manual grow process (first part of this post) does not even use the autogrow setting, and I grow the log file this way to a proper size that does not require any automatic growing, then the autogrow settings is not that important at all afterwards?

Third part of my question? Is the VLF count of 20 on a 40GB log file now too low? Is this the reason I read we should increment the file back up again in increments? What VLF count might be a GOOD (performant) number for a 40 G or 80 G log file?

Improve this question

1 Answer 1

Reset to default
2

VLF Calculations

The number of the VLFs is not different for a manual or automatic growth of the transaction log file.

SQL Server < 2014

Up to version 2014 of SQL Server the algorithm for the number of VLFs is as follows when you create, grow or auto-grow the TLog file:

  • Less than 1 MB : Complicated ignore
  • Up to 64 MB : 4 new VLFs
  • 64 MB to 1 GB : 8 new VLFs
  • More then 1 GB : 16 new VLFs

SQL Server 2014 and higher

As of SQL Server 2014 and higher the algorithm is as follows:

  • Growth of TLog less then 1/8 th size of current TLog size?
    • Yes: Create 1 new VLF
    • No: Use the above formula (for previous version)

Your calculation for the number of VLFs should be correct.

Is your database set to AUTOSHRINK? Are you executing any other maintenance tasks that could be resizing the TLOG size?

Auto-growth: yes or no?

If you manually grow your TLog file then indeed you could turn off the auto-growth setting. But be aware: What will happen if you are unavailable and the database has to grow? The database will grind to a halt with an error message.

Baseline Size

At some point in time your database will have a TLog size that will have adequate space for the general imports that occur and the everyday workload and the TLog/Diff/Full backups occurring.

Use this baseline size as a starting point when you create a new TLog from scratch. If the database's TLog is really huge then create a TLog with a base size of 8 GB and manually grow in 8 GB steps until you have reached the "baseline" size.

Then leave some additional space on disk to allow for one or two "auto-growths" of 8GB size.

This will leave you with the best settings according to the article Transaction Log VLFs – too many or too few? where they recommend VLFs to be 512 MB in size.

Disk Space

If you don't have infinite disk space, then ensure that the TLog file has a maximum size set, that will not fill you disk to the maximum.

Monitoring

The most important point is to have a monitoring tool in place that monitors the database's "free space", because you never know if a rogue process is going to do things you hadn't anticipated.

Transaction Log Physical Architecture

There is a good article on TechNet that explains the physical architecture of the Transaction Log. This gives you some insight as to why the TLog rolls over or why it might have to grow.

References

Important change to VLF creation algorithm in SQL Server 2014
Initial VLF sequence numbers and default log file size
Transaction Log VLFs – too many or too few?
Transaction Log Physical Architecture

Improve this answer
4
  • "The number of the VLFs is not different for a manual or automatic growth of the transaction log file." - I am not sure this is 100% correct. Meanwhile I figured out that I saw 16 new VLF after manual growth because it os ONE growing. Instead, I I let it grow auto in smaller increments I would see lots more VLFs. So while growing itself may work the same, the VLF number will be different depending on the autogrow-rate, right? Guess my misconception was to expect manual grow to perform as many autogrow steps that are required to get the desired file size.
    – Magier
    Jan 27, 2017 at 10:54
  • If you are manually growing in 2 GB steps, then you will have 16 VLFs. If you auto-grow in 2 GB steps, then you will still have 16 VLFs per step. If you reduce the size of the auto-growth step, then you can have more VLFs for 2 GB of growth. E.g. if you auto-grow in 64 MB steps up to 2048 MB (2 GB) then you will have 32 * 4 VLFs = 128 VLFs (2048 MB / 64 MB/step = 32 steps; 4 VLFs per 64 MB/step * 32 steps = 128 VLFs) You have to compare the same auto-growth setting with the same manual step growth.
    – John K. N.
    Jan 27, 2017 at 11:27
  • Ok. in case I want to re-size my log to 100 G and target to have 512 MB VLF size, I will do 8GB increments. should I start with an initial size of 8G or with more instead? To have 512 Mb consistant, would have to start with 8 GB and manually increment it 25 times, right?
    – Magier
    Jan 27, 2017 at 14:40
  • 1
    That is correct. The initial 8 GB TLog file will have 16 VLFs with a size of 512 MB per VLF. Each additional growth of 8 GB will result in 16 VLFs of 512 MB size being created. Once you reach 100 GB (or 96 GB) you will have 192 VLFs each sized 512 MB.
    – John K. N.
    Jan 27, 2017 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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