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?

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

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.