Excessive virtual log files in transaction log

| 7 Comments | 6,268 views
Categories:

VN:F [1.9.17_1161]
Rating: 2.3/5 (4 votes cast)

Excessive counts of Virtual Log Files (VLF) in a transaction log can lead to performance problems with the transaction log, including restore and recovery times. Excessive VLFs can occur when a transaction log is resized many times, through autogrowth for example.

The ideal value or threshold for this metric is environment dependent; excessively high values can cause high write times when writing to the transaction log.

If this metric indicates an increased number of VLFs in the transaction log, the transaction log should be rebuilt using the steps outlined in Kimberly Tripp’s blog post, 8 Steps to better Transaction Log throughput. Also see Transaction Log Physical Architecture.

Note: This metric relies on the use of an undocumented DBCC command from Microsoft. The output of this command may change (as it did from SQL Server 2008 to SQL Server 2012), so this script may not work correctly on future versions of SQL Server.

Note: This metric works on SQL Server 2000 SP4 and later versions.

Excessive virtual log files in transaction log, 2.3 out of 5 based on 4 ratings

Metric definition

Metric Name:
Transaction log fragmentation
Description:

Explanation: This custom metric measures the number of VLFs in the transaction log. A high number of VLFs can affect write performance and impact restore and recovery times.

Guideline values: The ideal value for this metric should be less than 25; excessively high values (greater than 50) can cause high write times when writing to the transaction log.

Possible solutions: If this metric indicates an increased number of VLFs in the transaction log, the transaction log should be rebuilt using the steps outlined in Kimberly Tripp’s blog post, 8 Steps to better Transaction Log throughput. Also see Transaction Log Physical Architecture.

Enter the T-SQL query that will collect data:
CREATE TABLE #tmp
    (
      RecoveryUnitID TINYINT ,
      FileId TINYINT ,
      FileSize BIGINT ,
      StartOffset BIGINT ,
      FSeqNo INT ,
      Status TINYINT ,
      Parity TINYINT ,
      CreateLSN NUMERIC(25, 0)
    );
 
 
--determine version of SQL Server, and trim column from temp table if version less than SQL Server 2012 (11)
DECLARE @Version VARCHAR(128);
SET @Version = CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'));
SET @Version = LEFT(@Version, CHARINDEX('.', @Version) - 1);
 
IF CAST(@Version AS INTEGER) < 11 
    BEGIN
        ALTER TABLE #tmp
        DROP COLUMN RecoveryUnitID
 
    END;
 
--get the results of DBCC LOGINFO
INSERT  INTO #tmp
        EXEC ( 'DBCC LOGINFO'
            );
 
SELECT  COUNT(*)
FROM    #tmp;
 
DROP TABLE #tmp;
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every day
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
High number of VLF in the transaction log
Description:

This alert is raised if excessive Virtual Log Files (VLF) counts occur. This can lead to performance problems with the transaction log, including restore and recovery times. Excessive VLFs can occur when a transaction log is resized many times, through autogrowth for example.

You can rebuild the transaction log by following the steps outlined in Kimberly Tripp’s blog post, 8 Steps to better Transaction Log throughput. Also see Transaction Log Physical Architecture.

Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:User determined threshold
Medium:User determined threshold
Low:Disabled
The value of the thresholds (too high or too low) need to be specified for your particular environment, use values suited to your server performance.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 2.3/5 (4 votes cast)
  • Chuck Lathrope

    On my large databases with bigger log files, I worry if over 100 to 150. Since one VLF is created with specific logic that Kimberly points out, we should be able to create a formula based on the size of the database that would give us the medium and high water marks that would work for any size database.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Courage Dog

    “… excessively high values (greater than 50) can cause high write times when writing to the transaction log.”

    That’s not true. Excessive number of VLFs will cause database recovery operations to take more time, same for log backup/restore operations.

    I don’t know where you found that, because that’s definitely neither in linked Kimberly article, nor in MSDN. Just read them carefully and don’t spread inaccurate info please.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Bill

    I have not seen a singe threshold value for how long a business can tolerate a database’s recovery (or logreader latency). Tolerance across businesses seems to vary on a logarithmic scale, much like Chuck pointed out. I have seen users start to raise concerns when the number of VLFs reached 10,000. The rate at which VLF count grows also impacts business tolerance (e.g.”the database was recovering quickly for months, until yesterday” or “replication had sub-3 second latencies, until yesterday”).

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Steve Mangiameli

    The number of VLFs is arbitrary and pretty much meaningless unless it is exorbitantly high – greater than 1000 – unless it is accompanied by the number of VLFs relative to the size of the log. 100 512MB VLFs in a 50GB log is very healthy!!! If you are going to monitor this type of metric, you really need to understand when it means trouble and when it does not.

    @Courage Dog, excessive VLFs can cause many issues with regards to log reading/writing beyond recovery operations. I’ve seen it! Consider all of the operations that use the log, like CDC and replication, not to mention Auto Growth when the log file cannot be reused.

    Too few VLFs is just as big a problem as too many and, again, it depends on the size of the log. However, in my opinion, too few VLFs can cause just as many problems as too many. Imagine 50 VLFs on a 250GB log; that would mean VLFs would be roughly 5GB!!! You would most likely be experiencing major headaches with anything related to the log!

    So make sure your log is sized correctly before you set up something like this. And then worry about getting the VLFs created correctly. To know the “right” size and number of VLFs, use these rules of thumb.

    You can use the 50ish VLF rule for a log file < 25GB. Anything larger than 25GB, a healthy log, in terms of VLF number, should be determined by log size / 512MB.

    Why 512MB? Because the log file will reuse itself, but only if a VLF, in its entirety, has been marked for reuse by either a full backup, a log backup, or issuing a checkpoint. Anything higher than 512MB limits these mechanism's effectiveness at marking VLFs as inactive and allowing reuse of the log.

    You may be asking yourself how you re-size a log you deem unhealthy. Basically, you want to shrink the log down to next to nothing. Once it's down to the smallest possible, you need to manually grow it to your target size, in increments calculated by the rules of thumb mentioned above. Then set the Auto Growth increment appropriately in MB not percentage!

    If you've done this and your VLF counts continue to creep north, you need to go back to figuring out what an appropriate log size is and then do this again.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Paul Randal

    Where do you get the recommendation of less than 25 from?

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Stuart Ainsworth

    At the time that I wrote this metric (over two years ago), I was trying to find a way illustrate the impact of having a large number of VLF’s; my recommendation was based on a number of stress tests that I performed in a lab environment using a SQL 2008 development server. Obviously, those tests were not comprehensive, and I should have written the recommendation to reflect that. Steve’s comment below provides some great advice on determining better thresholds for your environment.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Susan Rickman

    Thanks Stu for bringing this to everyone’s attention. And thanks for the script! I had a similar situation that Steve describes below with too few VLFs on a large log, which meant that the VLFs were excessively large! Like so many other things in SQL, it needs to be tweaked and reviewed, tweaked and reviewed. :)

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)