Msg 3930, Level 16, State 1, Line 13 The current transaction cannot be committed

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Purpose:

Explain the following T-SQL error:

Msg 3930, Level 16, State 1, Line 13
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Solution:

This error happens when T-SQL batch is trying to commit a transaction that was already abandoned.

When could this happen?

We need specific condition for that to happen

a. we need to be in try..catch block

b. the transaction need face an error (such as primary key constraint violation/ foreign key violation etc.)

c. we are trying to commit the transaction.

to make it easier to understand here is a short code snippet to repro the same error

--preperation. drop table if exists #tab1 create table #tab1(i int primary key) insert into #tab1 values(1) -- set xact abort to ON so the transaction will be invalidated if error happen set XACT_ABORT ON -- use try and catch block begin try begin transaction insert into #tab1 values(1) commit -- this commit will never happen, as we are violating the primary key constratint. end try begin catch print XACT_STATE() -- xact_state = (-1) indictae that the transaction is uncommitable. commit -- this will happen as part of the catch block, this will cause the exception of error 3930 to happen end catch

To handle such scenario you should evaluate the XACT_STATE() value before committing.

if it is shows -1 this means that you cannot attempt to commit the transaction and you should rollback the transaction instead.

More information:

XACT_STATE (Transact-SQL)

Leave a Reply Cancel reply