Up to 2014, the algorithm for how many VLFs you get when you create, grow, or auto-grow the log is based on the size in question:

  • Less than 1 MB, complicated, ignore this case.
  • Up to 64 MB: 4 new VLFs, each roughly 1/4 the size of the growth
  • 64 MB to 1 GB: 8 new VLFs, each roughly 1/8 the size of the growth
  • More than 1 GB: 16 new VLFs, each roughly 1/16 the size of the growth

Ref: http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

*******************************************************

VLF stands for Virtual Log File and the transaction log file is made up of one or more virtual log files. The number of virtual log files can grow based on the auto growth settings for the log file and how often the active transactions are written to disk.  Too many virtual log files can cause transaction log backups to slow down and can also slow down database recovery, and in extreme cases, even affect insert/update/delete performance.

*********************************************************

You can calculate the number of VLFs you will have.

If your initial log file size is less than 64MB then there will be 4 VLFs, between 64MB and less than 1GB there will be 8 VLFs, and a size of 1GB and larger will have 16 VLFs. The same respective number of VLFs will be created when the log file grows.

So for a 1 GB log file that is set to grow by 1 GB, if the current log file size is 3 GB, you should have 48 VLFs.

 

 

Advertisements