Home >> How to Shrink Transaction Log File of SQL Server 2000, 2005, 2008 & R2

 

How to Shrink Transaction Log File of SQL Server 2000, 2005, 2008 & R2

 

A transaction log in SQL Server is responsible for recording the changes made to the SQL database, whereas all user data is stored in a separate data file. It is a critical component of database architecture that is used to either undo a complete transaction or restore the entire database in the event of crash. It acts like a buffer to your database storing every item before it is actually committed to the data file. A checkpoint is added to the database transaction log at the end of a transaction. When you restart your SQL Server, it seeks the most recent checkpoint in the log and rolls forward all transactions written after that point. This helps to maintain database consistency.

 

These transaction logs are not actively maintained as any other database component. As operations on your database increase, the transaction logs tend to grow in size and consume huge amount of disk space. If the log file is allowed to grow indefinitely, you may soon run out of disk space where the log is stored. In case you do not perform timely maintenance of these logs, they are very likely to cause problems in the normal operation of your database.

 

Regular backups of the transaction log provide the best remedy to get past all these problems. In a transaction log backup, all the old and irrelevant log records are truncated. This is usually done by making the records inactive, so that they can be easily overwritten by new records. This indeed helps to control the size of the log file. In this practice, the space allocated to the log will be retained even if it is not used. This sets forth the need to shrink the log file in order to reduce its physical size.

 

The shrinking of a transaction log can be done using two DBCC statements, i.e. 'DBCC SHRINKDATABASE' and 'DBCC SHRINKFILE'. The first statement is run against the owning database while the latter is used against a specific transaction log. The SQL Server cannot shift log records at the end to the beginning of the file. A transaction log file is shrunk depending on the amount of free space at the end of the file. Generally, a simple recovery model is followed to shrink transaction logs. You can adjust the parameters used in the model according to your requirements.

 

'USE dbname
CHECKPOINT
DBCC SHRINKFILE(2, 500)' 

 

The first parameter indicates the file number for log file and the second parameter is the desired file size measured in MB.   

 

When you run the command several times, the head of the log file is moved to the beginning. This creates more and more free space at the end of the log file, thus making it easy for the SQL Server to shrink the log.

 

 

free download