PRB: Transaction Log Partially Truncated

ID Number: Q80629

1.10 1.11 4.20

OS/2

Summary:

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.

RESOLUTION

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.

Additional reference words: 1.10 1.11 4.20