How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005 (907511)
The information in this article 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
INTRODUCTIONIn 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 INFORMATIONIn 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 of these 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 "Introduction" section. To shrink a transaction log file that has little
free space in SQL Server 2005, follow these steps: - 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, run a Transact-SQL statement that is 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 that you are backing up. In this statement, <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' - Shrink
the transaction log file. To do this, run a Transact-SQL statement that is 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 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. - 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.
- Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be
similar to the target size.
In summary, the log manager's algorithm
for picking up 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.
REFERENCESFor more information, see the following topics in SQL Server 2005 Books Online: - Shrinking the transaction log
- DBCC SHRINKFILE (Transact-SQL)
- Truncating the transaction log
Modification Type: | Minor | Last Reviewed: | 11/1/2005 |
---|
Keywords: | kbsql2005engine kbinfo KB907511 kbAudDeveloper |
---|
|