How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
How can I find out how many VLFs my database has?
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):
** 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.