Wednesday , April 24 2024

Vlf(Virtual Log File) Count

What is Vlf(Virtual Log File)?

Vlf (Virtual Log File) is a virtual file, as the name implies.

The Transaction Log file with the ldf extension in SQL Server consists of one or more virtual log files.

If you create a database with default settings and the autogrowth and initial size of the database’s log file are not set appropriately, the number of virtual log files will be too many and will also cause performance bottlenecks.

High virtual log file (vlf) count

It increases the recovery time of the database.

For example, if the server or sql server service is restarted, the recovery process wil be performed when the database opening. If the number of Vlf is too many, this increases recovery time.

High number of vlf will also increase the log backup time. In some cases it also increases insert / update / delete times.

For example, if you have a database that has 5 MB initial size and auto growth of the database is 10%, then performance bottleneck will start when the size of the log file grows over time. Because, %10 of the 5 MB is too less and this cause high number of VLF. You can find the vlf count of your transaction log using the DBCC LOGINFO () command.

Find VLF Count

When you run the DBCC LOGINFO command, the number of records returned shows the number of VLFs in the log file. The number of VLF in my Test database is 4, as shown in the following screenshot.

You can use Kevin Riley’s script to check which databases on instance have this problem.

If your transaction log file has this problem, follow the steps below;

  • Backup the Transaction Log
  • Shrink the Transaction Log
  • Set Acceptable Initial Size(eg 1024 mb)
  • Set Acceptable Auto Growth(eg 512 mb)

To shrink the Transaction Log file, you can read the article titled “How To Shrink SQL Server Transaction Log“.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *