PRB: Transaction Log Partially Truncated

This article was previously published under Q80629
This article has been archived. It is offered "as is" and will no longer be updated.
If a user issues an ad hoc query with a BEGIN TRAN and thenneglects to issue a COMMIT TRAN, the transaction log will not betruncated from that point onward. This situation continues as longas the transaction remains active and has no corresponding commit.The problem will go away when the user either issues the COMMIT orcloses the connection and the transaction is aborted.
This problem occurs under certain conditions because of aninadvertently long-running transaction with no corresponding COMMITTRAN. The user will run out of transaction log space consistentlyfor no apparent reason. Attempts to alleviate the situation usingthe WITH TRUNCATE_ONLY or NO_LOG option of DUMP TRAN do not work.
The system administrator (SA) could get an indication of thisproblem if other users are not able to complete their queriesbecause of any locks that might be held by the offendingtransaction.

The SA should have all the clients issuing ad hoc queries executean explicit COMMIT TRAN. Normally, this should return the followingmessage:
The commit transaction has no corresponding BEGIN TRANSACTION
(Msg 3902, Level 16, State 1)

The absence of this message identifies the error and alleviates theproblem. If the users are not within close proximity, the lastresort is to recycle the server.
More information
Note that this problem can also occur if an application allows userinput within a transaction and the user does not respond in a timelyfashion. Another situation that could aggravate this problem is anynetwork platform subject to "sleeping processes" remaining on theserver after a connection has been broken. In such a case, shuttingdown and restarting the server might be the only option.

Article ID: 80629 - Last Review: 10/26/2013 04:19:00 - Revision: 4.0

Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbprogramming KB80629