Excessive virtual log files in transaction log

| 1 Comment | 3,028 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote 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 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.

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, 5.0 out of 5 based on 1 rating

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:50
Medium:25
Low:Disabled
Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more 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: 5.0/5 (1 vote 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)