Managing the Size of the Transaction Log File
In some cases, changing the size of a physical log file can be useful.
Monitoring Log Space Use
You can 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 is in need of 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
We recommend that avoid overloading the log disk.
Shrinking the Size of the Log File
Log truncation is essential because it frees disk space for reuse, but it does not reduce the size if the physical log file. To reduce its physical size, the log file must be shrunk to remove one or more virtual log files that do not hold any 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. For more information, see Shrinking the Transaction Log.
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 more information, see Factors That Can Delay Log Truncation.
For more information, see Shrinking the Transaction Log.
Adding or Enlarging 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. For information about adding files, see Adding and Deleting Data and Transaction Log Files.
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).
Optimizing the Size of the tempdb Transaction Log
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 and Optimizing tempdb Performance.
Controlling the Size of a Transaction Log File
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.
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.