Why does SQL Server need to read virtual log file?

Question

New Virtual Log Files are created when the existing ones are already active and new space is required. This brings us to the point where the value of the Virtual Log Files is created. So, whenever there is a crash and recovery condition, SQL Server first needs to read the Virtual Log File. 26 апр. 2017 г.

What is the use of VLF in SQL Server?

VLF stands for Virtual Log File. In SQL Server transaction log file is made up of one or more number of virtual log files. Too many virtual log files can cause transaction log backup to slow down as well as the database restore process.

How many VLF is too many?

The threshold for significantly impacting the recovery performance appears to be somewhere around ten thousand VLFs. No symptoms are felt when there are a few thousand VLFs or less. When there are around a hundred thousand VLFs, the symptoms become substantially noticed.

Why having too many or too few virtual log files VLFs can lead to performance problems?

For optimal performance a database should have roughly tens of VLFs. If there are hundreds or thousands of VLFs log file write performance can degrade and transaction log backup speed can degrade.

How do I reduce virtual logs?

Fixing a database with a high VLF count is a simple process:1Check the current size of the transaction log.2Backup the transaction log.3Shrink the transaction log to as close to 0 KB as possible.4Check that the VLF count is less than 50 (best if less than 10 at this point).Fixing High VLF Counts - Navisite

Expert · Answer