By: Brady Upton
Each physical transaction log file is divided internally into numerous virtual log files, or VLFs. The virtual log files are not a certain size nor can you specify how many VLF’s are in a physical log file. The Database Engine does this for us, but for performance reasons it tries to maintain a small number of virtual files.
System Performance is affected when the virtual log file is defined by small size and growth_increment values meaning if the log files grow to a large size because of many small increments, it will increase the number of virtual log files. This is why it’s a good reason to set Autogrow to a larger increment. If the log is set to grow at 1MB at a time it may grow continuously resulting in more and more virtual log files. An increased number of VLF’s can slow down database startup and log backup/restore operations.
There’s not a right or wrong number of VLF’s per database, but remember, the more you have the worse performance you may have. You can use DBCC LOGINFO to check the number of VLF’s in your database.
Andy Novick wrote an excellent tip on performance issues with a large number of VLF’s here.
Last Update: 3/25/2014
