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 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 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. 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 can shrink a log file 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.
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, 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)
Monitor log-file shrink events
Monitor log space
sys.database_files (Transact-SQL) (See the size, max_size, and growth columns for the log file or files.)
You can set log files to shrink 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
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
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.