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