VLF Creation Algorithm: How does it work ‘before & after’ SQL Server 2014

In this article, we will understand the logic behind the VLF creation in a Transaction Log File.

Every database has a Transaction Log file that keeps track of all the database changes made by each transaction and in short, it logs all the transactions. This physical database Log file is further divided into smaller ‘chunks’ or segments called VLFs (Virtual Log Files).

When SQL Server Engine allocates new space in a log file (resulting from a manual or auto-growth setting), it accomplishes the work using VLFs. Now, the count of the VLFs created is dependent on the size of the log growth triggered but not the actual log size. We will get a clear picture when we dig deep with the VLF creation algorithm.

SQL Server 2012 and earlier versions uses this Algorithm:

• Log growth <= 64 MB, then 4 VLFs are created
• Log growth between 64 MB and <= 1 GB, 8 VLFs
• 1 GB & above = 16 VLFs

SQL Server 2014 and above uses this Algorithm:

If the growth size is less than 1/8 the size of the current log size?

• Yes: create 1 new VLF equal to the growth size
• No: use the above algorithm

Going by the Paul Randal way of giving an example for the above two algorithms, if you create your log at 1 GB and it auto-grew in chunks of 0.5 GB till 200 GB, then

Case 1: SQL Server 2014 & above

• 8 VLFs from the initial log creation of 1 GB
• All growths up to the log being 4.5 GB would use the SQL Server 2012 & below VLF creation algorithm, so growths at 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5 GB would each add 8 VLFs = 56 VLFs
• All growths over 4.5 GB will only create 1 VLF per growth = (200 – 4.5) x 2 = 391 VLFs
• Total = 391 + 56 + 8 = 455 VLFs

Case 2: SQL Server 2012 & Earlier versions

• 8 VLFs from the initial log creation of 1 GB
• For the remaining 199 GB growth in 0.5 GB per auto-growth, there will be 199 x 2 X 8 = 3184 VLFs
• The total would be 3192 VLFs

Excessive VLFs can negatively impact all transaction log related activities. Simple tasks such as bringing a database online, performing a restore and running statements, such as INSERT, and UPDATE can become taxing to your server. Performance degradation can even be felt during transaction log backups if VLF counts are high.

If you are still stuck with older versions of SQL Server, it’s time you upgrade SQL Server to 2014 & above versions. We, at SQL Service, are more than happy to help you with that.