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

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 proce开发者_如何学Cdure 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?

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

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.

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;

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

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.

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