Shrinking the Transaction Log

Reducing the physical size of the transaction log requires shrinking the log file. Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. 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 about virtual log files, see Transaction Log Physical Architecture.

How Does Shrinking the Log File Work?

The unit of the size reduction is 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.

For a log file, the current size is the same as the pages that are used by the virtual 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 all of the inactive virtual log files. 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:

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:

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:

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.

When Is the Log Shrunk?

Shrinking the log can occur while the database is online. The size of the log files are physically reduced in the following situations:

• An autoshrink operation occurs.
• A DBCC SHRINKFILE statement referencing a log file is executed.
• A DBCC SHRINKDATABASE statement is executed.

To shrink a log file

• DBCC SHRINKFILE (Transact-SQL)
• How to: Shrink a File (SQL Server Management Studio)

To monitor log-file shrink events

• Log File Auto Shrink Event Class.

To monitor log space use

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

See Also

Concepts

Troubleshooting a Full Transaction Log (Error 9002)
Setting Database Options
Working with Transaction Log Backups
Transaction Log Truncation

Other Resources

BACKUP (Transact-SQL)
DBCC SHRINKDATABASE (Transact-SQL)
DBCC SHRINKFILE (Transact-SQL)
Space Allocation and Reuse

Help and Information

Getting SQL Server 2005 Assistance