Each transaction log file is made of smaller parts, known as virtual log files. The number of virtual log files per transaction log files is not fixed. Its size is determined dynamically when the transaction log file is executed. The number of virtual log files cannot be set or configured by the database administrator. If the auto-growth settings are not properly managed then, database can be forced to auto-grow that can cause serious performance issues. In the following section, we will discuss the causes and number of virtual log files can exist.
Impact of VLF on SQL Server Performance
In SQL Server, Transaction log files are set at 2MB at initial size. In addition, 10% of current size is the default growth value. At the time of creating SQL Server database, these options can be modified to accommodate the needs of database. The auto-growth option is optional which is turned on by default. SQL Server creates a database with unrestricted file growth. If the settings are not properly managed, then it will create an issue. Until, the auto-growth is finished, the server will stop all the processing. The auto-growth will take up the space that is not physically close to previous one due to physical organization of hard drive. It leads to physical fragmentation that causes the slower response in performance.
The Server should not have an excessive number of virtual log file inside the translation log. Large number of small virtual log file slows down the process of recovery, which database goes through on startup otherwise after restoring a backup. The threshold for significantly affecting the recovery performance appears to be around ten thousand virtual log files. When there are about hundred thousand virtual log files then, symptoms become significantly noticed.
Tips to Fix this Perfomance Issue:
- One can determine the no. of VLFs in specific database by checking the no. of records that are returned as resulted of the executed DBCC command within the text by using DBCC LOGINFO
- The number of virtual log files can be decreased by running the DBCC SHRINKFILE command.
In the discussion, performance issue with large number of Virtual Log Files in SQL Server Transaction is discussed. It provides guidance for users to have proper understanding about the virtual log file impact on SQL Server that results in slow down of the performance of the server.