SQL: Best Practice VLF Size
Author: jason
Date: 2014-06-13 09:55:04
Category: Technical

While the size of your transaction log is important, the size of the internal virtual log files is also important.

Go out and buy: Microsoft SQL Server 2012 Internals

Summary: Grow the Transaction Log in 8 GB chunks to limit the VLFs to a size of 512 MB each.

"When a log file is first create, it always has between 2 and 16 VLFs. If the file size is 1 MB or less, SQL server divides the size of the log file by the minimum VLF size (31 * 8 KB) to determine the number of VLFs. If the log file size is between 1 MB and 64 MB, SQL Server splits the log into four VLFs. If the log file is greater than 64 MB but less than or equal to 1 GB, eight VLFs are created. If the size is more than 1 GB, 16 VLFs are created. When the log grows, the same formula is used to determine how many new VLFs to add. A log always grows in units of entire VLFs and can be shrunk only to a VLF boundary."

"No ideal size or ideal number exists for VLFs. Try to keep your VLFs less than 1 GB in size, which can require that you create your initial log in chunks. So rather than create a 32 GB log, which would have 16 VLFs of 2 GB each, you can create it as an 8 GB file, and then alter its size three times to add 8 GB more each time. That gives you 32 GB, but the VLFs are 512 MB each."

Reference
Pages 178-180, Microsoft SQL Server 2012 Internals
http://technet.microsoft.com/en-us/library/ms179355(v=sql.105).aspx
http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009