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

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]asbegin    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 herelbexit:        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;