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.
VLF are totally managed internally by SQL server, I am talking about there size. If you have large amount of VLF's for the database its not a good sign.I guess you are referring to large amount of VLF as VLF fragmentation.
High amount of VLF can be attributed to fact that autogrowth settings are not proper for database may be very small and database has to allocate new vlf reach times it grows. Ofcourse VLF size would be small and eventually be many. 160 VLF count does not seems issue to me.Below blog will help you in further understanding
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
Best Answer
What is to many VLF?
Google around a bit and you find a bunch of different opinions on what constitutes to many. I think the better question is:
What is the right amount of VLFs for my requried log size?
Optimally transaction logs should be on a drive separate from the datafiles. There should be a single log file, that is sized correctly to meet reoccurring needs without auto growing. VLF should number between 4 and 50, with a maximum size of 500MB. For log files greater than 25GB special consideration is required. Other than during proactive maintenance they should not shrink or grow.
How do I tell how many I have and how big they are?
SQL Server writes logs sequentially. The logs are written into sub sections called Virtual Log Files (VLF). You can get information about the VLFs of a database with the undocumented DBCC LOGINFO command. The four fields to understand first are:
VLF are created when;
Status changes;
FSeqNo changes when:
How do I get the right number of VLF for my required log size?
First it depends on what version of SQL you are running. The DBA can not directly create VLFs.
Files size and number of VLF created per LSN:
What Grow means:
Each grow is resetting the current allocated size (shown as Initial size in the GUI). If the current size is 500MB and you change it to 1GB (1000MB) you are growing 500MB. This will create 8 new VLFs at 62.5MB each. All the existing VLF remain unchanged. If you had 76 VLF you will now have 84 VLF.
Ok, Now I am ready to see what I have and start changing them. How do I do it?
You can examine VLF on a single database, an entire server, or against a group in Central Management Servers (CMS).
For a single database, run DBCC LOGINFO to get a picture of the current VLF. The number of rows = the number of VLF.
You can use the GUI (Database Properties > Files) or the query below to see file size and location information. This query also gives used & free space information which is not available in the GUI
To examine all the databases on server or in a CMS group this query at github from the Microsoft tiger team, provides details on all database with greater than 50 VLF along with code and directions to make improvements.
https://github.com/Microsoft/tigertoolbox/tree/master/Fixing-VLFs
As of August 2017 it does not take into account the SQL 2014 VLF growth considerations outlined in Paul’s post https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/ Nor does it consider Orginal Equipment VLF. The Tiger solution offers generic solutions. On most occasions after looking at all available information I have chosen a slightly different solution than offered by the Fixing-VLFs query.
What size the log file should be, is to much to include here. For now let's assume you know what the best size is. The following should be done, off peak or during scheduled down time.
1.Set the auto grow to your first new allocated size
.
.
.
Return to Step 2 and repeat, until all the old growth VLFs are gone.
First Grow
.
.
.
.
SQL 2014+
Important Reminder: on SQL 2014 and newer “If the growth is less than 1/8 of the current log file (ldf) creates 1 VLF of the growth size” Example an 8GB Log file growing 1000MB is not going to create 8 new VLF at 125MB, it is will create 1 VLF at 1000MB, if it grows 1100MB it will create 16 new VLF at 68.75MB, Huge differences here (where 1GB = 1024MB).
Log File (ldf) greater than 25GB
Optimal configuration is often described as not more than 50 VLF at not more than 500MB each. Three grows of 8GB, give us about 48 VLF with an allocated space of 24GB. What happens if your log file needs to be 50 or 100GB? Do you increase VLF size or count? This document does not attempt to address those scenarios, I have found a few suggestions of allowing VLF to be up to 1GB, but when you get over the 24 or 25GB allocated file size there are just to many variables to offer a generic path to solution.
References: