Optimal Log File Growth and Virtual Log Files

2019-02-26 · by Hannah Vernon · in recovery, tools

Intro

The SQL Server Transaction Log is a critical component of each database, and should be managed pro-actively. Optimal log file growth is simple to setup, but knowing the right numbers to use for initial size, file growth rate, and maximum size are important for a well-functioning system.

Stuff™ Growng.

Warning

Before we get into the meat-and-potatoes of log file size management, make sure you’re not storing the transaction log on the same physical medium as the database data files. Make sure your database backups are stored separately, too. Having the log, data, and backups on separate drives decreases the possibility that a single drive failure will result in total loss of data.

About the Transaction Log

SQL Server uses the transaction log in the following ways:

Recovery of individual transaction data.
Recovery via rollback/rollforward of all incomplete transactions during SQL Server startup.
Rolling a restored database, file, filegroup, or page forward to just before the point of failure.
Supporting transactional replication.
Supporting Always On availability groups, log shipping, and database mirroring.

Microsoft’s Transaction Log Architecture Documentation has this high-level overview of the logical structure of the log:

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed transaction rollback.

Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed. The after image is a copy of the data after the operation has been performed.

The physical implemention of the transaction log:

Microsoft’s SQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

It’s important to proactively manage log file growth to ensure the number of VLFs doesn’t get out of hand. Having a very large number of VLFs can slow recovery time since SQL Server needs to scan every VLF (in serial and single threaded fashion) before actual recovery starts. Since discovery happens well before the recovery analysis phase begins, no messages indicating progress are logged in the error log, and no progress is displayed in sys.dm_exec_requests. With a very high number of VLFs, this initial discovery phase can take several hours even if there are no transactions in the log that need to be rolled forward or backward.

Virtual log file (VLF) creation follows this method:

If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x))

If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:

If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)

If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)

If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

So, we have two algorithms that affect VLF creation during log-growth events; the pre-2014 and post-2014 styles.

For SQL Server 2014+, the following table shows a handy list of values to use for the initial SIZE, MAXSIZE, and FILEGROWTH arguments in a CREATE DATABASE statement, along with the resulting VLF count and VLF size once the log file grows to its maximum size:

╔══════════╦═══════════╦════════╦═══════════╦══════════╦══════════╗
║ Initial  ║  Maximum  ║  File  ║   Total   ║ Minimum  ║ Maximum  ║
║ Log Size ║  Log Size ║ Growth ║ VLF Count ║ VLF Size ║ VLF Size ║
╠══════════╬═══════════╬════════╬═══════════╬══════════╬══════════╣
║       16 ║        16 ║     16 ║         8 ║        2 ║        2 ║
║       32 ║        32 ║     32 ║         8 ║        4 ║        4 ║
║       32 ║        64 ║     32 ║        12 ║        4 ║        8 ║
║       64 ║       128 ║     64 ║        12 ║        8 ║       16 ║
║       64 ║       256 ║     64 ║        20 ║        8 ║       16 ║
║      128 ║       512 ║    128 ║        32 ║       16 ║       16 ║
║      128 ║      1024 ║    128 ║        57 ║       16 ║      128 ║
║      256 ║      2048 ║    256 ║        57 ║       32 ║      256 ║
║      256 ║      4096 ║    256 ║        65 ║       32 ║      256 ║
║      512 ║      8192 ║    512 ║        65 ║       64 ║      512 ║
║      512 ║     16384 ║    512 ║        81 ║       64 ║      512 ║
║     1024 ║     32768 ║   1024 ║        81 ║      128 ║     1024 ║
║     1024 ║     65536 ║   1024 ║       113 ║      128 ║     1024 ║
║     2048 ║    131072 ║   2048 ║       161 ║      128 ║     2048 ║
║     2048 ║    262144 ║   2048 ║       225 ║      128 ║     2048 ║
║     4096 ║    524288 ║   4096 ║       225 ║      256 ║     4096 ║
║     4096 ║   1048576 ║   4096 ║       353 ║      256 ║     4096 ║
╚══════════╩═══════════╩════════╩═══════════╩══════════╩══════════╝

Numbers in these charts are expressed in Megabytes.

SQL Server versions prior to 2014 don’t fare so well with VLF counts:

╔══════════╦══════════╦════════╦═══════════╦══════════╦══════════╗
║ Initial  ║ Maximum  ║  File  ║   Total   ║ Minimum  ║ Maximum  ║
║ Log Size ║ Log Size ║ Growth ║ VLF Count ║ VLF Size ║ VLF Size ║
╠══════════╬══════════╬════════╬═══════════╬══════════╬══════════╣
║       16 ║       16 ║     16 ║         8 ║        2 ║        2 ║
║       32 ║       32 ║     32 ║         8 ║        4 ║        4 ║
║       32 ║       64 ║     32 ║        12 ║        4 ║        8 ║
║       64 ║      128 ║     64 ║        12 ║        8 ║       16 ║
║       64 ║      256 ║     64 ║        20 ║        8 ║       16 ║
║      128 ║      512 ║    128 ║        32 ║       16 ║       16 ║
║      128 ║     1024 ║    128 ║        64 ║       16 ║       16 ║
║      256 ║     2048 ║    256 ║        64 ║       32 ║       32 ║
║      256 ║     4096 ║    256 ║       128 ║       32 ║       32 ║
║      512 ║     8192 ║    512 ║       128 ║       64 ║       64 ║
║      512 ║    16384 ║    512 ║       256 ║       64 ║       64 ║
║     1024 ║    32768 ║   1024 ║       256 ║      128 ║      128 ║
║     1024 ║    65536 ║   1024 ║       512 ║      128 ║      128 ║
║     2048 ║   131072 ║   2048 ║      1016 ║      128 ║      256 ║
║     2048 ║   262144 ║   2048 ║      2040 ║      128 ║      256 ║
║     4096 ║   524288 ║   4096 ║      2040 ║      256 ║      512 ║
║     4096 ║  1048576 ║   4096 ║      4088 ║      256 ║      512 ║
╚══════════╩══════════╩════════╩═══════════╩══════════╩══════════╝

As you can see from the results above, newer versions of SQL Server create far lower numbers of VLFs, which should make recovery faster and more predictable.

Code

I use the following script to generate the results above:

DECLARE @SQLServerVersion smallint = 2014; --2008, 2012, 2014, 2017, etc.
DECLARE @max_log_size int;
DECLARE @current_log_size int;
DECLARE @growth int;
DECLARE @TotalVLFCount int;
DECLARE @MinVLFSize int;
DECLARE @MaxVLFSize int;
IF OBJECT_ID(N'tempdb..#sizes', N'U') IS NOT NULL
DROP TABLE #sizes;
CREATE TABLE #sizes
(
    initial_log_size int NOT NULL
    , max_log_size int NOT NULL
    , growth int NOT NULL
    , TotalVLFCount int NULL
    , MinVLFSize int NULL
    , MaxVLFSize int NULL
    , PRIMARY KEY CLUSTERED (initial_log_size, max_log_size)
);

INSERT INTO #sizes (initial_log_size, max_log_size, growth)
VALUES 
      (16, 16, 16)
    , (32, 32, 32)
    , (32, 64, 32)
    , (64, 128, 64)
    , (64, 256, 64)
    , (128, 512, 128)
    , (128, 1024, 128)
    , (256, 2048, 256)
    , (256, 4096, 256)
    , (512, 8192, 512)
    , (512, 16384, 512)
    , (1024, 32768, 1024)
    , (1024, 65536, 1024)
    , (2048, 131072, 2048)
    , (2048, 262144, 2048)
    , (4096, 524288, 4096)
    , (4096, 1048576, 4096);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.max_log_size, s.growth
FROM #sizes s
ORDER BY s.max_log_size;

OPEN cur;
FETCH NEXT FROM cur INTO @max_log_size, @growth;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Max Log Size: ' + CONVERT(varchar(10), @max_log_size);
    SET @TotalVLFCount = 8;
    SET @current_log_size = @growth;
    PRINT '@Current_Log_Size: ' + CONVERT(varchar(10), @current_log_size);
    SET @MinVLFSize = @growth / 8;
    SET @MaxVLFSize = @growth / 8;
    PRINT 'Growth: (8)' + CONVERT(varchar(10), @growth / 8);
    PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);
    PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);
    WHILE @current_log_size < @max_log_size
    BEGIN
        SET @current_log_size = @current_log_size + @growth;
        IF @SQLServerVersion >= 2014 AND @growth < = @current_log_size / 8
        BEGIN 
            SET @TotalVLFCount = @TotalVLFCount + 1
            IF @growth < @MinVLFSize SET @MinVLFSize = @growth;
            IF @growth > @MaxVLFSize SET @MaxVLFSize = @growth;
            PRINT 'Growth: (1)' + CONVERT(varchar(10), @growth);
            PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);
            PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);
        END
        ELSE
        BEGIN
            IF @growth < = 64
            BEGIN
                SET @TotalVLFCount = @TotalVLFCount + 4
                IF @growth / 4 < @MinVLFSize SET @MinVLFSize = @growth / 4;
                IF @growth / 4 > @MaxVLFSize SET @MaxVLFSize = @growth / 4;
                PRINT 'Growth: (4) ' + CONVERT(varchar(10), @growth / 4);
                PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);
                PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);
            END
            ELSE
            BEGIN
                IF @growth < = 1024
                BEGIN
                    SET @TotalVLFCount = @TotalVLFCount + 8
                    IF @growth / 8 < @MinVLFSize SET @MinVLFSize = @growth / 8;
                    IF @growth / 8 > @MaxVLFSize SET @MaxVLFSize = @growth / 8;
                    PRINT 'Growth: (1024-8) ' + CONVERT(varchar(10), @growth / 8);
                    PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);
                    PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);
                END
                ELSE
                BEGIN
                    SET @TotalVLFCount = @TotalVLFCount + 16
                    IF @growth / 16 < @MinVLFSize SET @MinVLFSize = @growth / 16;
                    IF @growth / 16 > @MaxVLFSize SET @MaxVLFSize = @growth / 16;
                    PRINT 'Growth: (16) ' + CONVERT(varchar(10), @growth / 16);
                    PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);
                    PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);
                END
            END
        END
        PRINT @current_log_size;
    END
    UPDATE #sizes 
    SET TotalVLFCount = @TotalVLFCount 
        , MinVLFSize = @MinVLFSize
        , MaxVLFSize = @MaxVLFSize
    WHERE max_log_size = @max_log_size;
    PRINT N'';
    PRINT N'';
    FETCH NEXT FROM cur INTO @Max_log_size, @growth;
END
CLOSE cur;
DEALLOCATE cur;

SELECT [Initial Log Size] = s.initial_log_size
    , [Max Log Size] = s.max_log_size
    , [File Growth] = s.growth
    , [Total VLF Count] = s.TotalVLFCount
    , [Min VLF Size] = s.MinVLFSize
    , [Max VLF Size] = s.MaxVLFSize
FROM #sizes s;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

DECLARE @SQLServerVersion smallint = 2014; --2008, 2012, 2014, 2017, etc.

DECLARE @max_log_size int;

DECLARE @current_log_size int;

DECLARE @growth int;

DECLARE @TotalVLFCount int;

DECLARE @MinVLFSize int;

DECLARE @MaxVLFSize int;

IF OBJECT_ID(N'tempdb..#sizes', N'U') IS NOT NULL

DROP TABLE #sizes;

CREATE TABLE #sizes

(

initial_log_size int NOT NULL

, max_log_size int NOT NULL

, growth int NOT NULL

, TotalVLFCount int NULL

, MinVLFSize int NULL

, MaxVLFSize int NULL

, PRIMARY KEY CLUSTERED (initial_log_size, max_log_size)

);

INSERT INTO #sizes (initial_log_size, max_log_size, growth)

VALUES

(16, 16, 16)

, (32, 32, 32)

, (32, 64, 32)

, (64, 128, 64)

, (64, 256, 64)

, (128, 512, 128)

, (128, 1024, 128)

, (256, 2048, 256)

, (256, 4096, 256)

, (512, 8192, 512)

, (512, 16384, 512)

, (1024, 32768, 1024)

, (1024, 65536, 1024)

, (2048, 131072, 2048)

, (2048, 262144, 2048)

, (4096, 524288, 4096)

, (4096, 1048576, 4096);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

SELECT s.max_log_size, s.growth

FROM #sizes s

ORDER BY s.max_log_size;

OPEN cur;

FETCH NEXT FROM cur INTO @max_log_size, @growth;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Max Log Size: ' + CONVERT(varchar(10), @max_log_size);

SET @TotalVLFCount = 8;

SET @current_log_size = @growth;

PRINT '@Current_Log_Size: ' + CONVERT(varchar(10), @current_log_size);

SET @MinVLFSize = @growth / 8;

SET @MaxVLFSize = @growth / 8;

PRINT 'Growth: (8)' + CONVERT(varchar(10), @growth / 8);

PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);

PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);

WHILE @current_log_size < @max_log_size

BEGIN

SET @current_log_size = @current_log_size + @growth;

IF @SQLServerVersion >= 2014 AND @growth < = @current_log_size / 8

BEGIN

SET @TotalVLFCount = @TotalVLFCount + 1

IF @growth < @MinVLFSize SET @MinVLFSize = @growth;

IF @growth > @MaxVLFSize SET @MaxVLFSize = @growth;

PRINT 'Growth: (1)' + CONVERT(varchar(10), @growth);

PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);

PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);

END

ELSE

BEGIN

IF @growth < = 64

BEGIN

SET @TotalVLFCount = @TotalVLFCount + 4

IF @growth / 4 < @MinVLFSize SET @MinVLFSize = @growth / 4;

IF @growth / 4 > @MaxVLFSize SET @MaxVLFSize = @growth / 4;

PRINT 'Growth: (4) ' + CONVERT(varchar(10), @growth / 4);

PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);

PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);

END

ELSE

BEGIN

IF @growth < = 1024

BEGIN

SET @TotalVLFCount = @TotalVLFCount + 8

IF @growth / 8 < @MinVLFSize SET @MinVLFSize = @growth / 8;

IF @growth / 8 > @MaxVLFSize SET @MaxVLFSize = @growth / 8;

PRINT 'Growth: (1024-8) ' + CONVERT(varchar(10), @growth / 8);

PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);

PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);

END

ELSE

BEGIN

SET @TotalVLFCount = @TotalVLFCount + 16

IF @growth / 16 < @MinVLFSize SET @MinVLFSize = @growth / 16;

IF @growth / 16 > @MaxVLFSize SET @MaxVLFSize = @growth / 16;

PRINT 'Growth: (16) ' + CONVERT(varchar(10), @growth / 16);

PRINT '@MinVLFSize = ' + CONVERT(varchar(10), @MinVLFSize);

PRINT '@MaxVLFSize = ' + CONVERT(varchar(10), @MaxVLFSize);

END

PRINT @current_log_size;

END

UPDATE #sizes

SET TotalVLFCount = @TotalVLFCount

, MinVLFSize = @MinVLFSize

, MaxVLFSize = @MaxVLFSize

WHERE max_log_size = @max_log_size;

PRINT N'';

FETCH NEXT FROM cur INTO @Max_log_size, @growth;

END

CLOSE cur;

DEALLOCATE cur;

SELECT [Initial Log Size] = s.initial_log_size

, [Max Log Size] = s.max_log_size

, [File Growth] = s.growth

, [Total VLF Count] = s.TotalVLFCount

, [Min VLF Size] = s.MinVLFSize

, [Max VLF Size] = s.MaxVLFSize

FROM #sizes s;

Let me know if you found the above script useful, if you spot a problem, or simply want to suggest a correction!

This post is part of our series on recovery.

Tags: log size, t-log, transaction log, vlf

4 Responses

jadarnel27@gmail.com

2019-02-27 at 11:34 AM · Reply →

Have you seen this? https://www.brentozar.com/archive/2017/06/separating-data-log-files-make-server-reliable/

It’s an interesting take on the idea of separating data and log files, although it’s more focused on reliability (uptime) rather than data loss. The gist is that having two drives means two points of failure (and thus increased chances of downtime). One of the many tradeoffs we make, I guess =)

Loading...
1. Hannah Vernon
  
  2019-02-27 at 3:42 PM · Reply →
  
  Yah, interesting points made by Brent, as always. I generally prefer physical separation because it increases performance as well. If the log drive dies, you still have the data files, and you can recreate the log if you know what you’re doing. If the data drive dies, you can replay the log against a prior backup. If the backup drive dies, you just get a new one and take more backups. I understand the statistics behind failure rates, and Brent’s math *is* correct, but this isn’t strictly only about failure rates. If you have data and logs on the same volume, and you have no reliable backup, good luck when that volume goes down! I think the great point raised by Brent is to *think* instead of just blindly *doing*.
  
  Loading...
Hannah Vernon

2019-02-28 at 8:41 AM · Reply →

The Microsoft SQL Server Tiger Team has a script for reducing the number of VLFs in databases over at https://github.com/Microsoft/tigertoolbox/tree/master/Fixing-VLFs

Loading...
Transaction Log Structure - SQL Server Science

2019-04-12 at 4:31 PM ·

[…] Optimal Log File Growth and Virtual Log Files for details about sizing Virtual Log […]

Loading...

Optimal Log File Growth and Virtual Log Files

Intro

Warning

About the Transaction Log

Code

Related

4 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta