PRB: Transaction Log Partially Truncated

• Microsoft SQL Server versions 6.0, 6.5, 6.5 Service Pack 1 and later, 7.0, 7.0 Service Pack 1

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)

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.

Additional query words:

Keywords : kbprg SSrvServer
Version : winnt:6.0,6.5,6.5 Service Pack 1 and later,7.0,7.0 Service Pack 1
Platform : winnt
Issue type :