Managing the Transaction Log

This topic discusses the routine management of transaction log space to prevent the transaction log from becoming full.

Log truncation, which is automatic under the simple recovery model, is essential to keep the log from filling. The truncation process reduces the size of the logical log file by marking as inactive the virtual log files that do not hold any part of the logical log. In some cases, however, physically shrinking or expanding the physical log file is useful.

Note

A small autogrowth increment on a log file can also 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).

In This Section

Topic Description

Transaction Log Truncation

Contains information about log truncation.

Managing the Size of the Transaction Log File

Contains information about monitoring the size of the transaction log, shrinking the transaction log, adding or removing a transaction log file, and optimizing the tempdb transaction log growth rate.

Factors That Can Delay Log Truncation

Describes factors that can delay log truncation.

See Also

Concepts

Adding and Deleting Data and Transaction Log Files
Applying Transaction Log Backups
Coding Efficient Transactions
Transaction Log Physical Architecture
Troubleshooting a Full Transaction Log (Error 9002)
Working with Transaction Log Backups

Other Resources

BACKUP (Transact-SQL)
CHECKPOINT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance