Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What is a Virtual Log File (VLF)?



By:

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools