Shrinking the Transaction Log
If you know that a transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file.
Shrinking can occur only while the database is online and, also, while 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
Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up. However, truncation can be delayed by a number of factors. For more information, see Factors That Can Delay Log Truncation.
To shrink a log file (without shrinking database files)
To monitor log-file shrink events
To monitor log space
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 How to: View or Change the Properties of a Database (SQL Server Management Studio)—use the Auto Shrink property on the Options page—or ALTER DATABASE SET Options (Transact-SQL)—use the AUTO_SHRINK option.
How Does Shrinking the Log File Work?
Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file. For example, if you have a 600 megabyte (MB) log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but the file cannot be reduced to sizes such as 433 MB or 525 MB. A virtual log file that holds any active log records, that is, an active virtual log file, is part of the logical log, and it cannot be removed. For more information, see Transaction Log Physical Architecture.
Note
The Database Engine chooses the size of the virtual log file dynamically when log files are created or extended. For more information, see Transaction Log Physical Architecture.
For a log file, the current size is the same as the total size of the pages that are used by the virtual log files. Note, however, that pages are not used by the log files. Virtual log files that hold any part of the logical log cannot be freed. If all the virtual log files in a log file hold parts of the logical log, the file cannot be shrunk. Shrinking is not possible until after log truncation marks one or more of the virtual log files as inactive.
A shrink-file operation can remove only inactive virtual log files. If no target size is specified, a shrink-file operation removes only the inactive virtual log files beyond the last active virtual log file in the file. If a target size is specified, a given shrink-file operation removes only enough inactive virtual log files to approach but not exceed the target size. After shrinking, the log file is typically somewhat larger than the target size, and it will never be smaller. The virtual log files make it difficult to predict how much the log file will actually shrink.
When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual log files from the end of the log file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log file boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file that contains six 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.
A DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to shrink the physical log file to the requested size:
- If no part of the logical log in the virtual log files extends beyond the target_size mark, the virtual log files that come after the target_size mark are freed and the successful DBCC statement is completed with no messages.
If part of the logical log in the virtual logs does extend beyond the target_size mark, the SQL Server Database Engine frees as much space as possible and issues an informational message. The message tells you what actions you have to perform to remove the logical log from the virtual logs at the end of the file. After you perform this action, you can then reissue the DBCC statement to free the remaining space.
For example, assume that a 600 MB log file that contains six virtual log files has a logical log that starts in virtual log 3 and ends in virtual log 4 when you run a DBCC SHRINKFILE statement with a target_size of 275 MB, which is three-quarters of the way into virtual log 3:
.gif "Log file with 6 virtual log files before shrinking")
Virtual log files 5 and 6 are freed immediately, because they do not contain part of the logical log. However, to meet the specified target_size, virtual log file 4 should also be freed, but it cannot because it holds the end portion of the logical log. After freeing virtual log files 5 and 6, the Database Engine fills the remaining part of virtual log file 4 with dummy records. This forces the end of the log file to the end of virtual log file 1. In most systems, all transactions starting in virtual log file 4 will be committed within seconds. This means that the entire active portion of the log is moved to virtual log file 1. The log file now looks similar to this:
.gif "Log file is reduced to 4 virtual files")
The DBCC SHRINKFILE statement also issues an informational message that states that it could not free all the space requested, and that you can run a BACKUP LOG statement to free the remaining space. After the active portion of the log moves to virtual log file 1, a BACKUP LOG statement truncates the entire logical log that is in virtual log file 4:
.gif "Log file results after truncating the log")
Because virtual log file 4 no longer holds any portion of the logical log, you can now run the same DBCC SHRINKFILE statement with a target_size of 275 MB. Virtual log file 4 is then freed and the size of the physical log file is reduced to the size you requested.
Note
Certain factors, such as a long-running transaction, can keep virtual log files active for an extended period. This can restrict log shrinkage or even prevent the log from shrinking at all. For more information, see Factors That Can Delay Log Truncation.
See Also