Manage the size of the transaction log file

Article
02/28/2023
6 minutes to read

Applies to: SQL Server

This topic covers how to monitor SQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

Monitor log space use

Monitor log space use by using sys.dm_db_log_space_usage. This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

Important

Avoid overloading the log disk. Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.

Shrink log file size

To reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space. You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. In some cases, shrinking the log may not be possible until after the next log truncation.

Note

Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. For information, see Factors that can delay log truncation.

Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.

Important

Before shrinking the transaction log, keep in mind Factors that can delay log truncation. If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. For more information, see the Recommendations in this topic.

Shrink a log file (without shrinking database files)

Monitor log-file shrink events

Log File Auto Shrink Event Class.

Monitor log space

sys.dm_db_log_space_usage (Transact-SQL)
sys.database_files (Transact-SQL) (See the size, max_size, and growth columns for the log file or files.)

Add or enlarge a log file

You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.

To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. Adding a log file allows the log to grow.
To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

For more information, see the Recommendations in this topic.

Optimize tempdb transaction log size

Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. This can reduce the performance of the tempdb transaction log.

You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. For more information, see tempdb Database.

Control transaction log file growth

Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Note the following:

To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
To change the growth increment, use the FILEGROWTH option. A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

For more information, see the Recommendations in this topic.

Recommendations

Following are some general recommendations when you are working with transaction log files:

The automatic growth (autogrow) increment of the transaction log, as set by the FILEGROWTH option, must be large enough to stay ahead of the needs of the workload transactions. The file growth increment on a log file should be sufficiently large to avoid frequent expansion. A good pointer to properly size a transaction log is monitoring the amount of log occupied during:
- The time required to execute a full backup, because log backups cannot occur until it finishes.
- The time required for the largest index maintenance operations.
- The time required to execute the largest batch in a database.

When setting autogrow for data and log files using the FILEGROWTH option, it might be preferred to set it in size instead of percentage, to allow better control on the growth ratio, as percentage is an ever-growing amount.

In versions prior to SQL Server 2022 (16.x), transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
Starting with SQL Server 2022 (16.x) (all editions) and in Azure SQL Database, instant file initialization can benefit transaction log growth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization.
As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. The default values for FILEGROWTH option are:

Version	Default values
Starting with SQL Server 2016 (13.x)	Data 64 MB. Log files 64 MB.
Starting with SQL Server 2005 (9.x)	Data 1 MB. Log files 10%.
Prior to SQL Server 2005 (9.x)	Data 10%. Log files 10%.

A small growth increment can generate too many small VLFs and can reduce performance. To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.
A large growth increment can generate too few and large VLFs and can also affect performance. To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.
Even with autogrow enabled, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query. For more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options
Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.
Log files can be set to shrink automatically. However this is not recommended, and the auto_shrink database property is set to FALSE by default. If auto_shrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused.
- The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.
- For information about changing the setting of the auto_shrink property, see View or Change the Properties of a Database and ALTER DATABASE SET Options (Transact-SQL).