SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

Question

I'm having a similar issue to The current transaction cannot be committed and cannot support operations that write to the log file, but I have a follow-up question.

The answer there references Using TRY...CATCH in Transact-SQL, which I'll come back to in a second...

My code (inherited, of course) has the simplified form:

SET NOCOUNT ON
SET XACT_ABORT ON

CREATE TABLE #tmp

SET @transaction = 'insert_backtest_results'
BEGIN TRANSACTION @transaction

BEGIN TRY

    --do some bulk insert stuff into #tmp

END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION @transaction
    SET @errorMessage = 'bulk insert error importing results for backtest '
        + CAST(@backtest_id as VARCHAR) +
        '; check backtestfiles$ directory for error files ' + 
        ' error_number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
        ' error_message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(200)) +
        ' error_severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +
        ' error_state ' +  CAST(ERROR_STATE() AS VARCHAR) + 
        ' error_line: ' + CAST(ERROR_LINE() AS VARCHAR)
    RAISERROR(@errorMessage, 16, 1)
    RETURN -666
END CATCH

BEGIN TRY

    EXEC usp_other_stuff_1 @whatever

    EXEC usp_other_stuff_2 @whatever

    -- a LOT of "normal" logic here... inserts, updates, etc...

END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION @transaction
    SET @errorMessage = 'error importing results for backtest '
        + CAST(@backtest_id as VARCHAR) +
        ' error_number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
        ' error_message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(200)) +
        ' error_severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +
        ' error_state ' +  CAST(ERROR_STATE() AS VARCHAR) + 
        ' error_line: ' + CAST(ERROR_LINE() AS VARCHAR)
    RAISERROR(@errorMessage, 16, 1)
    RETURN -777

END CATCH

RETURN 0

I think I have enough information to just play with it and figure it out myself... unfortunately reproducing the error is proving damn near impossible. So I'm hoping that asking here will help clarify my understanding of the problem and solution.

This stored procedure is, intermittently, throwing errors like this one:

error importing results for backtest 9649 error_number: 3930 error_message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. error_severity: 16 error_state 1 error_line: 217

So obviously the error is coming from the 2nd catch block

Based on what I've read in Using TRY...CATCH in Transact-SQL, I think what's happening is that when the exception is thrown, the use of XACT_ABORT is causing the transaction to be "terminated and rolled back"... and then the first line of the BEGIN CATCH is blindly attempting to roll back again.

I don't know why the original developer enabled XACT_ABORT, so I'm thinking the better solution (than removing it) would be to use XACT_STATE() to only roll back if there is a transaction (<>0). Does that sound reasonable? Am I missing something?

Also, the mention of logging in the error message makes me wonder: Is there another problem, potentially with configuration? Is our use of RAISEERROR() in this scenario contributing to the problem? Does that get logged, in some sort of case where logging isn't possible, as the error message alludes to?

Chasseur

This is good: RETURN -666; – Kilauea

Answer 1

You always need to check for XACT_STATE(), irrelevant of the XACT_ABORT setting. I have an example of a template for stored procedures that need to handle transactions in the TRY/CATCH context at Exception handling and nested transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
               @message = ERROR_MESSAGE(), 
               @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

Tracietracing

Your template assumes transactions inside the try block; we have multiple try blocks inside 1 transaction. – Chasseur

@Adam: It's about how you handle the XACT_STATE and the transactions in the CATCH block. You can have multiple try blocks in one transaction using this very template. The idea is to understand how transactions and catch blocks interact, and as a bonus you also get the handling of nested transactions and savepoints, which is very useful in batch processing, as it gives the ability to resume the rest of the batch even if one entry had failed. – Tracietracing

I've gone ahead and wrapped the rollback statement in an if XACT_STATE() <> 0, but only time will tell if that resolves it for us. Guess I'll go ahead and accept your answer for now. – Chasseur

This answer deserves more upvotes than it has. The template is very clear and useful, with applicability beyond just this question. – Braunschweig

Why not just say if @@trancount = 0 begin transaction? What's the benefit to declaring the variable? – Janycejanyte

@DevinLamothe because the variable is checked again later – Tracietracing

@Remus Yep, I see it now. Thanks! – Janycejanyte

Thought I would add a comment. I was receiving this error for a stored procedure that was being run for my biztalk solution via a send/receive port. This answer and changing the Ambient Transaction value fixed my issue. Thanks – Vex

We have a proc that has some error logging in a catch but has no explicit concept of transactions itself. It's being called from another proc that has transactions wrapped around it (specifically a tSQLt unit test). The error logging in the first proc is getting an error "current transaction cannot be committed...". Do you suggest we make the first proc aware of the fact that it could be used in a transaction to fix this problem? Thanks – Schwitzer

Hi After adding insert statement in catch Block I am getting new error "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0". I am calling this SP from Python <code> raw_Conn = DBConn.get_rawConnection() cursor = raw_Conn.cursor() cursor.execute("""SET NOCOUNT ON;EXEC dbo.my_Sp @BatchNumber=?""", batch_number) cursor.commit() cursor.close() </code> – Guglielma

Answer 2

There are a few misunderstandings in the discussion above.

First, you can always ROLLBACK a transaction... no matter what the state of the transaction. So you only have to check the XACT_STATE before a COMMIT, not before a rollback.

As far as the error in the code, you will want to put the transaction inside the TRY. Then in your CATCH, the first thing you should do is the following:

 IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION @transaction

Then, after the statement above, then you can send an email or whatever is needed. (FYI: If you send the email BEFORE the rollback, then you will definitely get the "cannot... write to log file" error.)

This issue was from last year, so I hope you have resolved this by now :-) Remus pointed you in the right direction.

As a rule of thumb... the TRY will immediately jump to the CATCH when there is an error. Then, when you're in the CATCH, you can use the XACT_STATE to decide whether you can commit. But if you always want to ROLLBACK in the catch, then you don't need to check the state at all.

Enthymeme

Whilst the subject is incredibly nuanced; the statement "you can always ROLLBACK a transaction" is incorrect - at least using the example given. The XACT_STATE should be checked before rolling back if the XACT_ABORT flag is ON. E.g. If a named transaction is created with XACT_ABORT ON, subsequently rolling back the named transaction, as your example shows will result in an error. In this scenario, XACT_STATE must be checked to gauge which command to execute. Simply checking @@TRANCOUNT as shown will result in error. – Shear

Answer 3

We were getting this error and thought it had to do with Begin and End Blocks, and Try's and Commit's in the proc. As we took pieces out of the proc and begin/end blocks and ran them separately we found that it was a simple incompatible data conversion problem that came up because a feeder system changed what had always been its input. So sure, it could be the coding of your begin/end/try's but in our case root cause wasn't a logging error that is in the error message. Why the data conversion error didn't come up first, I don't know. I would particularly suspect my root cause if your proc has been running a long time and suddenly starts throwing the poster's error message, not in the midst of a development cycle.

Systematics

Answer 4

I have encountered this error while updating records from table which has trigger enabled. For example - I have trigger 'Trigger1' on table 'Table1'. When I tried to update the 'Table1' using the update query - it throws the same error. THis is because if you are updating more than 1 record in your query, then 'Trigger1' will throw this error as it doesn't support updating multiple entries if it is enabled on same table. I tried disabling trigger before update and then performed update operation and it was completed without any error.

DISABLE TRIGGER Trigger1 ON Table1;
Update query --------
Enable TRIGGER Trigger1 ON Table1;

Lesson

Answer 5

I encountered a similar issue to the above and was receiving the same error message. The above answers were helpful but not quite what I needed, which was actually a bit simpler.

I had a stored procedure that was structured as below:

SET XACT_ABORT ON

BEGIN TRY

    --Stored procedure logic
    
    BEGIN TRANSACTION
      --Transaction logic
    COMMIT TRANSACTION

    --More stored procedure logic

END TRY

BEGIN CATCH

    --Handle errors gracefully

END CATCH

TRY...CATCH was used to handle errors in the stored procedure logic. Just one part of the procedure contained a transaction, and if an error occurred during this it would not get picked up by the CATCH block, but would error out with the SQL Transaction Error message.

This was resolved by adding another TRY...CATCH wrapper that would ROLLBACK the transaction and THROW the error. This meant any errors in this step could be handled gracefully in the main CATCH block, as per the rest of the stored procedure.

SET XACT_ABORT ON

BEGIN TRY

    --Stored procedure logic
    
    BEGIN TRY
      BEGIN TRANSACTION;
        --Transaction logic
      COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
      ROLLBACK;
      THROW;
    END CATCH

    --More stored procedure logic

END TRY

BEGIN CATCH

    --Handle errors gracefully

END CATCH

Furniture

Hi, just curious, where do you do your Rollback? – Sabu

Answer 6

None of this helped me so here is what fixed my issue. A teammate configured a server trigger to monitor DDL changes. Once I disabled it, I could install the package then I enabled it again and package is still working.

Gooseflesh

Answer 7

The definition of a transactions is that all the code inside the transaction scope must be atomic, that consists of an unit of code to be done until the end (COMMIT) or reversed to the beginning (ROLLBACK).

With this definition, is it possible to have a partial transaction inside an atomic transaction?

Of course NOT !

But the problem is when a procedure that have a transaction call another procedure that have also its own transaction.... What happen ? How can I manage that ?

As an example in pseudo code :

CREATE PROCEDURE P_ADD_PHONE ....
...
BEGIN TRANSACTION
....
COMMIT
...
GO

CREATE PROCEDURE P_ADD_ADDRESS ...
...
BEGIN TRANSACTION
....
COMMIT
...
GO

CREATE PROCEDURE P_ADD_PERSONNE ...
...
BEGIN TRAN
...
EXEC P_ADD_PHONE .... 
...
EXEC P_ADD_ADDRESS ...
...
COMMIT
...
GO

You must understand that there is two concept about transaction :

• IMPLICIT transaction : a BEGIN TRAN / { COMMIT / ROLLBACK } is placed aroud any command that modify or read(1) the database (CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE...)
• EXPLICIT transaction : you must write your own BEGIN TRAN and finalyze wherever you want with COMMIT or ROLLBACK.

When nested transactions are used, BEGIN TRANSACTION elevate the transaction status from IMPLICIT to EXPLICIT and set @@TRANCOUNT to 1. Any new BEGIN TRANSACTION do near the same, but in this case, elevating from EXPLICIT to EXPLICIT does nothing more except that @@TRANCOUNT is incremented of 1.

When a COMMIT is done, the @@TRANCOUNT is decreased of 1 and if @@TRANCOUNT si 0 then an effective COMMIT is done. In other case COMMIT do nothing at all except the decraese...

When ROLLBACK is called, the ROLLBACK is immediately done and the @@TRANCOUNT is set to 0. But when the transactions status is not at the EXPLICIT level (@@TRANCOUNT = 0), the ROLLBACK issues an error because there is no transaction to roll back ...

This behaviour is known is RDBMS literature as the asymmetric model of nested transaction(2)... In this model if you want to commit all nested transaction you need to do execute as many COMMIT as you executed BEGIN TRANSACTION, but the first ROLLBACK immediately ends the explicit transaction status... By comparison think to a train that must goes by LONDON, PARIS, BERLIN, VIENNA. If every interval is a subtransaction, then when everything does well (COMMIT) the train can arrive safely to the end... But if in any interval between to station anything goes wrong do you want to continue the transportation in a collapsed train ?

By the way thinks that the concept of transaction (implicit vs explicit) is just a state of the user session that can be reached by the function XACT_STATE()... XACT refers to the standard ISO SQL pseudo name for a user eXplicit transACTion.

(1) yes transactions is also used for reading the data. As an example, if a divide by zero is done in only one row of a gigantic set of data (billions of rows) the result will be no row at all ! But in the case of READ transaction the transaction log is not used...

(2) in the symmetric model of nested transaction each BEGIN TRANSACTION must have its corresponding COMMIT or ROLLBACK... Then only the final COMMIT or ROLLBACK is really applied... Which is a nonsense !

Chung

Answer 8

Had the exact same error in a procedure. It turns out the user running it (a technical user in our case) did not have sufficient rigths to create a temporary table.

EXEC sp_addrolemember 'db_ddladmin', 'username_here';

did the trick

Weider

For us, the problem occurred because the user had admin rights on the main database but did not have sufficient rights on a 2nd database referenced by one of the table triggers. – Askwith