> Too many VLFs - How do I truncate them?

**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:

* FileSize; The size of the VLF in bytes.  Optimally these should be about the same size.  Except for those with a CreateLSN of zero.  
* FSeqNo; The sequence order the VLFs were last written to.  
* Status; Has two values, 0 = VLF is available for reuse, 2 = VLF is not available. 
* CreateLSN; The Log Sequence Number (LSN) when the VLF was created.  If the Value is 0 it the VLF was created at the same time as the database (Original Equipment) 

VLF are created when;

* The database is created for the first time (CreateLSN = 0) generally it will have between 2 and 8 original equipment VLFs
* The logfile is manually grown. i.e. (ALTER DATABASE [MyDB] MODIFY FILE (NAME = N' MyDB_log', SIZE = 51200KB) using T-SQL or the GUI (Graphical User Interface)
* The logfile auto grows because all the current VLFs have a “Status” of 2 and more transactions need to be written to the logs.

Status changes;

* To 2, when the content is not fully committed and/or in the case of full recovery the transaction has not been backed up.
* To 0, when the VLF is available to be overwritten.  All the content is committed and/or the transactions are backed up.

FSeqNo changes when:

* The VLF is written to. The value indicates the sequence the VLF was written to. A FSeqNo value of 0 indicates a VLF that has never been used. 
* The highest value FSeqNo will always have a Status Value of 2, it is the VLF currently being written to.

**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:

* The size and number of VLF created is handled by an algorithm based on growth size and SQL version. 
* SQL 2008 R2 and earlier, same as below but avoid growing in increments or multiples of 4GB 
 * https://connect.microsoft.com/SQLServer/feedback/details/357502/transaction-log-file-size-will-not-grow-exactly-4gb-when-filegrowth-4gb 
* SQL 2012 and earlier
 * Grow 1 to 64MB, creates 4 new VLF, each about 1/4 of the growth
 * Example; 50MB grow gives 4 VLF of 12.5MB
 * Grow 64MB to 1GB, creates 8 new VLF, each about 1/8 of the growth
 * Example; 250MB grow gives 8 VLF of 31.25MB
 * Grow 1GB or larger, creates 16 new VLF, each about 1/16 of the growth
 * Example; 1GB grow gives 16 VLF of 62.5MB
* 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: 10GB ldf growing 1GB gives 1 VLF of 1GB
 * If the growth is more than 1/8 of the current log file uses the SQL 2012 algorithm
 * Example: 10GB ldf growing 2GB gives 16 VLF of 125MB

**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. 

	USE [MyDB]
	GO
	DBCC LOGINFO

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

	select file_id
	, type_desc
	, name
	, substring([physical_name],1,3) AS [Drive]
	, physical_name
	, state_desc
	, size / 128 as 'AllocatedSizeMB'
	, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  
	, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
	, growth / 128 as 'GrowthSettingMB'

	 from sys.database_files
	 order by type_desc Desc, name

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

* Why? If/when the shrink gets you 4 VLF with a total allocated size of 1MB, and anything happens on the database, the logs will auto grow.  

.

	USE [master]
	GO
	ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', FILEGROWTH = 4000MB )
	GO

2. Shrink the log
* Why? Other than the original equipment VLF the goal is to create, new VLF that are properly sized.  Using SHRINKFILE with a size of 1, will eventually clear all except the original equipment 

.

	USE [MyDB]
	GO
	DBCC SHRINKFILE (N'MyDB_log' , 1)
	GO

3. See how many VLF are left
* Why? There are several scenarios, you may still have several hundred VLF or only the original equipment VLF left, you may have got down to 1MB and had an immediate auto 

.

	USE [MyDB]
	GO
	DBCC LOGINFO

4. Cycle the t-logs, if you have not cleared off all the old VLF. 
* Why? If anything other than original equipment VLF and/or brand new auto grows remain you need to cycle to get the currently active VLF to the top of the list. 
* Code: (varies) Run on demand t-log backups or CHECKPOINT
5. Return to Step 2 and repeat, until all the old growth VLFs are gone. 
* Why? Depending on multiple variables, it may take 3 or 4 runs through to get all the old grows off. 

6.	First Grow
* Why? Create your first set of planned VLFs

.

	USE [master]
	GO
	ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 4000MB )
	GO

7. See how many VLF you have now
* Why? See if you got what you expected

.

	USE [MyDB]
	GO
	DBCC LOGINFO

8. Second Grow
* Why? If you decided, you need more, repeat as needed

.

	USE [master]
	GO
	ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 4000MB )
	GO

9. Set a reasonable Auto Grow
* Why? If everything goes perfect, and you have taken everything into account it will never auto grow.  You need to decide what is going make the best sense on your system. 

.

	USE [master]
	GO
	ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', FILEGROWTH = 250MB )

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:

* https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/ 
* http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/21/exploring-the-transaction-log-structure.aspx 
* https://technet.microsoft.com/en-us/library/ms190411(v=sql.105).aspx 
* https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/ 
* https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/ 
* https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/ 
* https://www.brentozar.com/archive/2015/05/how-to-see-recent-autogrowth-events-on-databases-sql-server/