How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?


What is a virtual log file?

SQL Server divides the transaction log file for each database into smaller chunks, called 'virtual log files' (or VLFs for short). Their primary function is as truncation markers at a log backup, i.e. the SQL Server will only clear (and mark available for re-use) VLFs that are completely empty. MSDN has a piece on Transaction Log - Physical Architecture.

What determines the number of VLFs?

Each time a log file grows (whether via autogrowth or manual growth), the new section of the log file is divided into a number of VLFs, purely based on the size of the new section (the existing transaction log is left alone). So, small autogrowth settings (i.e. the 10% autogrowth that is the default) will cause a large number of VLFs to be created.

What are the implications of a large number of VLFs?

The primary issue a large number of VLFs causes are:

  • Slow recovery (recovery is a phase during database restore where completed transactions are written to the data pages and incomplete transactions are rolled back).
  • Slow log-reader performance in a database mirroring setup.
  • Slow performance during database snapshot creation (note that this also includes certain functions of DBCC since they use database snapshots in the background to facilitate consistency checks without blocking).

How can I find out how many VLFs my database has?

DBCC LOGINFO will return 1 row for each VLF in your database's transaction log. This question has a couple of useful scripts for applying that across all databases on a server.

How many VLFs is too many?

That's a judgment call you'll have to make for yourself. My personal rule of thumb is that under 50 isn't worth messing with, and over 100 (or so) and I fix autogrowth settings and make a mental note to (in the next maintenance window) shrink and regrow the log (as below).

Help! I have eleventy billion VLFs and my database recovery takes all day!

Short outline (from Kimberly Tripp's blog):

  • Make sure you're the only connection active in the database (so, do this during a maintenance window)
  • Backup the transaction log to disk (using BACKUP LOG)
  • Run DBCC SHRINKFILE with TRUNCATEONLY to shrink the log file to the smallest size possible.
  • Run ALTER DATABASE [...] MODIFY FILE [...] SIZE=newsize to resize your transaction log back up in a single step**.

** Note - if you have a very large log file (tens of GBs or more), you may want to resize in multiple steps to get an appropriate number of VLFs with an appropriate size to avoid excessively 'chunky' log backups. Since VLFs are the unit of truncation they also determine the log backup sizes, as detailed in Kim's blog.

| improve this answer | |
  • Thanks, do you think that multiple VLFs would more obviously hinder performance in a high OLTP environment where data is being changed regularly, a large number of commits to go through? – the_good_pony Aug 12 '13 at 11:13
  • Large numbers of VLFs are only really notable in the situations I outlined above (recovery, snapshots, mirroring). They might hinder performance a bit in an OLTP environment, but only marginally so. – Simon Righarts Aug 12 '13 at 20:36
  • 1
    For me, Recovery of 20+ hours was what convinced me of the importance of maintaining a low (sub 1,000) VLF count. – Max Vernon Aug 13 '13 at 1:03
  • @SimonRigharts I'm curious. Does a large number of VLFs impact regular transaction performance at all? Or does it only impact backups and restores? – Chris Aldrich Nov 23 '16 at 15:12
  • @ChrisAldrich There may be some tiny level of overhead on a transaction if it spans a VLF boundary (but if there is I've not heard of it), but as detailed in the question the only time you'll actually notice the impact of a large number of VLFs is during recovery, mirroring or snapshots. – Simon Righarts Jan 27 '17 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.