Database Reference
In-Depth Information
and it is created or grown by this amount, you end up with 12,800 VLFs, with each VLF
being 16MB. At or before this point, you'd start to notice performance problems.
Table 6.2
Number of VLFs Allocated per Chunk Size
Let's take a look at another example: Suppose you have a 128GB log file created as
128GB to begin with. This file will have 16 VLFs, and each VLF will be 8GB. This
means that each VLF can only be cleared at more than 8GB and when completely
inactive. The process of clearing the log segment will likely have a direct impact on the
performance of the database.
Caution
The number of VLFs in your transaction log file should
not
exceed 10,000.
Above this level, there will be a noticeable performance impact. In an
environment with log shipping, mirroring, or AlwaysOn, the number of VLFs will
have an impact on the entire related group of SQL Servers. See Microsoft KB
2653893 (
http://support.microsoft.com/kb/2653893
)
and SAP Note 1671126
To avoid the performance problems covered previously, you should ensure your VLF
size is between 256MB and 512MB. This will guarantee that even if your transaction
log were to reach the maximum size of 2TB, it will not contain more than 10,000 VLFs.
To achieve this, you can preset your log file to either 4GB or 8GB and grow it (either
manually or with Auto Grow) by the same amount each time. If we take the example of
the 128GB transaction log, you would initially create a 8GB log file and then grow it by
8GB fifteen times. This will leave you with the 128GB log file and 256 VLFs within
that log file, at 512MB each. You should set your transaction log file Auto Grow size to
be the same as whatever growth increment you have decided upon.
Tip
One of the quickest and easiest ways to find out how many VLFs there are in your
database and to find out more about your log files is to execute the query DBCC
LOGINFO. The number of rows returned is the number of VLFs.
