Optimal Log File Growth and Virtual Log Files

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.

Optimal Log File Growth

     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:

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.