04 July 2012

Excessive virtual log files in transaction log

by Stuart Ainsworth

18,413 3 Performance

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.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

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.

Metric definition

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: You can use the 50 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.

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.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			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;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
				
						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;

Instances to collect from

Select all

Databases to collect from

Collection frequency

86400

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

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 threshholds

Default threshold values

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

1 collection

Alert is

Enabled

18,413 Rate

Click to rate this post!

[Total: 9 Average: 2.9]

Excessive virtual log files in transaction log

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics