Manage the Size of the Transaction Log File

Applies To: SQL Server 2016
This topic contains information about how to monitor the size of a SQL Server transaction log, shrink the transaction log, add 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 DBCC SQLPERF (LOGSPACE). This command returns information about the amount of log space currently used, and indicates when the transaction log needs truncation. For more information, see DBCC SQLPERF (Transact-SQL). For information about the current size of a log file, 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. For more information, see sys.database_files (Transact-SQL).
Important! Avoid overloading the log disk!

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 will not need.You can shrink a log file can occur only while the database is online and at least one virtual log file 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 virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. For information about factors that can delay log truncation, see The Transaction Log (SQL Server).

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

• DBCC SHRINKFILE (Transact-SQL)
• Shrink a File

Monitor log-file shrink events

• Log File Auto Shrink Event Class.

Monitor log space

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

Note

Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. If autoshrink 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 autoshrink property, see View or Change the Properties of a Database—use the Auto Shrink property on the Options page—or ALTER DATABASE SET Options (Transact-SQL)—use the AUTO_SHRINK option.

Add or enlarge a log file

Alternatively, 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.

• 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).

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

You can use the ALTER DATABASE (Transact-SQL) 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. A small autogrowth increment on a log file can reduce performance. The file growth increment on a log file should be sufficiently large to avoid frequent expansion. The default growth increment of 10 percent is generally suitable.

For information on changing the file-growth property on a log file, see ALTER DATABASE (Transact-SQL).

• 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.