Introduction In Microsoft SQL Server 2005, you can shrink the transaction log files in a database to remove unused pages. The database engine will reuse space efficiently. However, when a transaction log file grows unexpectedly, it may be necessary to manually shrink the transaction log file.
This article describes how to use the DBCC SHRINKFILE statement to manually shrink transaction log files in a SQL Server 2005 database in full recovery model. The method you use to shrink a transaction log file in SQL Server 2005 may be different from the method you use to shrink a transaction log file in SQL Server 2000. For more information about how to shrink transaction log files in SQL Server 2000, click the article number below to view the corresponding article in the Microsoft Knowledge Base:
272318 ( http://support.microsoft.com/kb/272318/ ) INF: Shrinking transaction logs in SQL Server 2000 using DBCC SHRINKFILE
Back to the top
More information In SQL Server 2005, the shrink operation (DBCC SHRINKFILE) immediately attempts to shrink the specified transaction log file to the required size. To manually shrink transaction log files in full recovery model, first back up the transaction log files. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file.
Generally, shrinking transaction log files in SQL Server 2005 is faster than shrinking transaction log files in SQL Server 2000. The reason is that the SQL Server 2005 Log Manager creates or reuses inactive virtual log files based on the physical disk storage order. Therefore, the inactive portion of a transaction log file is usually located at the end of the file.
For example, a transaction log file might contain 100 virtual log files, but only use 2 virtual log files. SQL Server 2000 stores the first virtual log file used at the beginning of the transaction log file and the second virtual log file used in the middle of the transaction log file. In order to shrink the transaction log file to only 2 virtual log files, SQL Server will fill the remainder of the second virtual log file with virtual log entries. SQL Server moves the beginning of the logical log to the next available virtual log file specified by the log manager. The log manager may create a virtual log file in the middle of the transaction log file, before the last active virtual log file. In this case, multiple log backup operations and multiple shrink operations must be used to successfully shrink the transaction log file to 2 virtual log files. Worst case scenario, 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 immediately shrink the transaction log file to 2 virtual log files by executing a DBCC SHRINKFILE statement. This is because the SQL Server 2005 Log Manager creates 2 virtual log files in the order in which they are stored on the physical disk. Both virtual log files are located at the beginning of the transaction log file.
When you try to shrink a transaction log file that has very little free space in SQL Server 2005, you must perform another log backup operation. This additional log backup operation truncates the transaction log file to a smaller size. When shrinking transaction log files in SQL Server 2000, in addition to performing the above three steps, you must also perform this log backup operation. For more information, see the Microsoft Knowledge Base article mentioned in the "Introduction" section. To shrink a transaction log file with very little free space in SQL Server 2005, follow these steps: 1. Back up the transaction log files so that most of the active virtual log files are inactive. Therefore, inactive virtual log files can be deleted in a later step. To do this, run a Transact-SQL statement similar to 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 to be backed up. In this statement, <BackupFile> is a placeholder for the full path to 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 similar to 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. In this statement, <TargetSize> is a placeholder for the target size of the transaction log file. Target size must be reasonable. For example, you cannot shrink a transaction log file to 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 mentioned in step 1 to make more virtual log files inactive.
4. Run the DBCC SHRINKFILE statement mentioned in step 2. After doing this, the transaction log file should be close to the target size.
In summary, the algorithm that the Log Manager uses to select the next virtual log file has changed in SQL Server 2005. Therefore, shrinking transaction log files in SQL Server 2005 differs from shrinking transaction log files in SQL Server 2000 in the following ways: • If the log file has a large amount of free space, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000 Shrinking transaction log files is faster.
• Shrinking a transaction log file in SQL Server 2005 is the same as shrinking a transaction log file in SQL Server 2000 if the log file has no free space.
• Shrinking a transaction log file in SQL Server 2005 requires one more log backup operation than performing this operation in SQL Server 2000 if the log file has very little free space.