PRB: Transaction Log Partially Truncated

Article translations Article translations
Article ID: 80629 - View products that this article applies to.
This article was previously published under Q80629
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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 "sleeping processes" 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.

Properties

Article ID: 80629 - Last Review: October 26, 2013 - Revision: 4.0
Applies to
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbprogramming KB80629

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com