How to shrink the transaction log file in SQL Server 2005

Article translations Article translations
Article ID: 907511 - View products that this article applies to.
Expand all | Collapse all

Summary

In Microsoft SQL Server 2005, you can shrink a transaction log file in a database to remove unused pages. The database engine reuses space effectively. However, when a transaction log file grows unexpectedly, you may have to shrink the transaction log file manually.

This article describes how to use the DBCC SHRINKFILE statement to shrink the transaction log file manually under the full recovery model in a SQL Server 2005 database. The method that you use to shrink the transaction log file in SQL Server 2005 may differ from the method that you use to shrink the transaction log file in SQL Server 2000. For more information about how to shrink the transaction log file in SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:
272318 Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE

More information

In SQL Server 2005, a shrink operation (DBCC SHRINKFILE) tries to shrink the specified transaction log file to the requested size immediately. To shrink the transaction log file manually under the full recovery model, first back up the transaction log file. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file.

Typically, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000. The reason is that the SQL Server 2005 log manager creates or reuses inactive virtual log files by following the physical disk storage order. Therefore, the inactive part of the transaction log file is usually at the end of the file.

For example, the transaction log file may have 100 virtual log files, and only 2 virtual log files are used. SQL Server 2000 may store the first used virtual log file at the start of the transaction log file and the second used virtual log file in the middle of the transaction log file. To shrink the transaction log file to only 2 virtual log files, SQL Server fills the remaining part of the second virtual log file by using dummy log entries. SQL Server moves the start of the logical log to the next available virtual log file that is specified by the log manager. The log manager may create a virtual log file in the middle of the transaction log file just ahead of the last active virtual log file. In that case, you have to use multiple log backup operations and multiple shrink operations to successfully shrink the transaction log file to 2 virtual log files. In the worst case of this example, you may have to use 50 log backup operations and 50 shrink operations to successfully shrink the transaction log file to 2 virtual log files.

However, in SQL Server 2005, you can perform one DBCC SHRINKFILE statement to shrink the transaction log file immediately to 2 virtual log files. You can do this because the SQL Server 2005 log manager creates 2 virtual log files by following the physical disk storage order. Both virtual log files are at the start of the transaction log file.

When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size. This log backup operation is in addition to the three steps that you perform to shrink the transaction log file in SQL Server 2000. For more information, see the Microsoft Knowledge Base article that is mentioned in the "Summary" section. To shrink a transaction log file that has little free space in SQL Server 2005, follow these steps:
  1. Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, start SQL Server Management Studio and then run a Transact-SQL statement that resembles the following Transact-SQL statement. 
    BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'
    Note In this statement, <DatabaseName> is a placeholder for the name of the database that you are backing up, and <BackupFile> is a placeholder for the full path of the backup file.

    For example, run the following Transact-SQL statement.
    BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'
  2. Shrink the transaction log file. To do this, run a Transact-SQL statement that resembles the following Transact-SQL statement.
    DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
    Note In this statement, <FileName> is a placeholder for the name of the transaction log file, and <TargetSize> is a placeholder for the target size that you want the transaction log file to be. The target size must be reasonable. For example, you cannot shrink the transaction log file to a size that is less than 2 virtual log files.
  3. If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive.
  4. Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be close to the target size.
In summary, the log manager's algorithm for obtaining the next virtual log file changed in SQL Server 2005. Therefore, shrinking the transaction log file in SQL Server 2005 may differ from shrinking the transaction log file in SQL Server 2000. 
  • If a log file has lots of free space, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000.
  • If a log file has no free space, shrinking the transaction log file in SQL Server 2005 is the same as shrinking the transaction log file in SQL Server 2000.
  • If a log file has little free space, you may have to perform an additional log backup operation in SQL Server 2005 than you have to perform in SQL Server 2000.

References

For more information about how to shrink the transaction log, go to the Shrinking the Transaction Log Microsoft Developer Network (MSDN) website.

For more information about the DBCC SHRINKFILE statement, go to the DBCC SHRINKFILE (Transact-SQL) MSDN website.

For more information about transaction log truncation, go to the Transaction Log Truncation MSDN website.

Properties

Article ID: 907511 - Last Review: July 12, 2013 - Revision: 5.1
Applies to
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbsqlsetup kbsql2005engine kbinfo KB907511

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com