How to shrink the transaction log file in SQL Server 2005

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.