You are currently offline, waiting for your internet to reconnect

How to shrink the transaction log file in SQL Server 2005

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.
T-LOG
Properties

Article ID: 907511 - Last Review: 07/12/2013 08:14:00 - Revision: 5.1

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

  • kbsqlsetup kbsql2005engine kbinfo KB907511
Feedback