Reducing the VLF count for a SQL Server database

Reducing the number of VLFs in the log files of a database

Having many hundreds or thousands of VLFs in a database's log files increases the recovery time of it. It also is an indication that the log file has grown too frequently by a small amount. I will try to explain how to reduce the number of VLFs and set the growth pattern of the log file to a suitable size

Step 1: Find out how many VLFs are in the log files

Run DBCC LOGINFO under the concerned database's context

The row count of the output indicates the number of VLFs

Step 2: Backup the transaction log

Backup the transaction log to clear it of all the committed transactions. It best to make sure that this manual log backup joins the chain of automated log backups to keep it intact. It's a good idea to simply run the automated job for log backups provided there aren't too many other databases that are part of the job and it is ok to do so

Step 3: Shrink the log file

Shrink the log file using DBCC SHRINK FILE (logical file name, TargetSize)

The target size can be accessed by running DBCC SQLPERF ('logspace'). The log space used and log size should give a rough estimation of the how much space can be gained

If the DBCC SHRINKFILE does not shrink the log file enough, backup the log file again to increase the number of inactive VLFs and run DBCC SHRINKFILE again

Step 4

Re-size the log file and change its auto growth factor

Run the following with a suitable initial size, max size and growth factor

Preferably, set the auto growth factor to 1 GB and thus reducing the number of VLFs created when the file grows