Microsoft KB Archive/80629

= PRB: Transaction Log Partially Truncated =

Article ID: 80629

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 6.5 Service Pack 1
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 1

-



This article was previously published under Q80629



SYMPTOMS
If a user issues an ad hoc query with a BEGIN TRAN and then neglects to issue a COMMIT TRAN, the transaction log will not be truncated from that point onward. This situation continues as long as the transaction remains active and has no corresponding commit. The problem will go away when the user either issues the COMMIT or closes the connection and the transaction is aborted.



CAUSE
This problem occurs under certain conditions because of an inadvertently long-running transaction with no corresponding COMMIT TRAN. The user will run out of transaction log space consistently for no apparent reason. Attempts to alleviate the situation using the WITH TRUNCATE_ONLY or NO_LOG option of DUMP TRAN do not work.



WORKAROUND
The system administrator (SA) could get an indication of this problem if other users are not able to complete their queries because of any locks that might be held by the offending transaction.

The SA should have all the clients issuing ad hoc queries execute an explicit COMMIT TRAN. Normally, this should return the following message:

The commit transaction has no corresponding BEGIN TRANSACTION

(Msg 3902, Level 16, State 1)

The absence of this message identifies the error and alleviates the problem. If the users are not within close proximity, the last resort is to recycle the server.



MORE INFORMATION
Note that this problem can also occur if an application allows user input within a transaction and the user does not respond in a timely fashion. Another situation that could aggravate this problem is any network platform subject to &quot;sleeping processes&quot; remaining on the server after a connection has been broken. In such a case, shutting down and restarting the server might be the only option.

Keywords: kbprogramming KB80629

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.