PRB: The Transaction Log File Shrinks Unexpectedly After Each Transaction Log Backup (323022)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q323022

SYMPTOMS

A transaction log file shrinks to a certain value after each transaction log backup operation although the autoshrink option of the database is not set.

CAUSE

When the DBCC SHRINKDATABASE or DBCC SHRINKFILE statement does not shrink the size of the transaction log file to the target size, the target size is set as a positive number in the log file header. If the target size of the log file header is not the default value, each transaction log backup operation repeatedly shrinks the log file to the target size. You also experience this behavior with the DBCC SHRINKFILE statement without the target size parameter. The statement shrinks the log as much as possible.

WORKAROUND

To prevent the transaction log backup from shrinking the log file, run the DBCC SHRINKFILE statement and use a target size that is similar to the actual log file size. (The shrinking is successful in the next transaction log backup operation.)

MORE INFORMATION

For additional information about the behavior of the DBCC SHRINKFILE statement, see the "DBCC SHRINKFILE (T-SQL)" topic in SQL Server 7.0 Books Online. The following is an excerpt from this topic:

"Unlike data files, the shrinking of log files is not immediate. Each log file is marked with the target size of the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file and bring its size as close to the target size as possible."



The target size of the transaction log file is set back to default value after a successful shrinking operation that shrinks the file size to the target size.

STATUS

This behavior is by design.
Modification Type:MinorLast Reviewed:4/29/2003
Keywords:kbprb KB323022 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.