Trigger Error: the Current Transaction Cannot Be Committed and Cannot Support Operations That Write to the Log File

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]
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

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

This error occurs when you use a try/catch block inside of a transaction. Let's consider a trivial example:

SET XACT_ABORT ON

IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO #t (i) VALUES (1)
    INSERT INTO #t (i) VALUES (2)
    INSERT INTO #t (i) VALUES (3)
    INSERT INTO #t (i) VALUES (1) -- dup key error, XACT_ABORT kills the batch
    INSERT INTO #t (i) VALUES (4) 

COMMIT  TRAN
SELECT * FROM #t

When the fourth insert causes an error, the batch is terminated and the transaction rolls back. No surprises so far.

Now let's attempt to handle that error with a TRY/CATCH block:

SET XACT_ABORT ON
IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO #t (i) VALUES (1)
    INSERT INTO #t (i) VALUES (2)
    BEGIN TRY
        INSERT INTO #t (i) VALUES (3)
        INSERT INTO #t (i) VALUES (1) -- dup key error
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
    END CATCH  
    INSERT INTO #t (i) VALUES (4)
    /* Error the Current Transaction cannot be committed and 
    cannot support operations that write to the log file. Roll back the transaction. */

COMMIT TRAN
SELECT * FROM #t

We caught the duplicate key error, but otherwise, we're not better off. Our batch still gets terminated, and our transaction still gets rolled back. The reason is actually very simple:

TRY/CATCH blocks don't affect transactions.

Due to having XACT_ABORT ON, the moment the duplicate key error occurs, the transaction is doomed. It's done for. It's been fatally wounded. It's been shot through the heart...and the error's to blame. TRY/CATCH gives SQL Server...a bad name. (sorry, couldn't resist)

In other words, it will NEVER commit and will ALWAYS be rolled back. All a TRY/CATCH block can do is break the fall of the corpse. We can use the XACT_STATE() function to see if our transaction is committable. If it is not, the only option is to roll back the transaction.

SET XACT_ABORT ON -- Try with it OFF as well.
IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO #t (i) VALUES (1)
    INSERT INTO #t (i) VALUES (2)

    SAVE TRANSACTION Save1
    BEGIN TRY
        INSERT INTO #t (i) VALUES (3)
        INSERT INTO #t (i) VALUES (1) -- dup key error
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
        IF XACT_STATE() = -1 -- Transaction is doomed, Rollback everything.
            ROLLBACK TRAN
        IF XACT_STATE() = 1 --Transaction is commitable, we can rollback to a save point
            ROLLBACK TRAN Save1
    END CATCH  
    INSERT INTO #t (i) VALUES (4)

IF @@TRANCOUNT > 0
    COMMIT TRAN
SELECT * FROM #t

Triggers always execute within the context of a transaction, so if you can avoid using TRY/CATCH inside them, things are much simpler.

For a solution to your problem, a CLR Stored Proc could connect back to SQL Server in a separate connection to execute the dynamic SQL. You gain the ability to execute the code in a new transaction and the error handling logic is both easy to write and easy to understand in C#.

Current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

I see a couple of potential errors that could be preventing the commit but without knowing more about the structure these are just guesses:

1. The update clause in the nested if is trying to update a column (or set of columns) that must be unique. Because the check only verifies that at least one item exists but does not limit that check to making sure only one item exists

   IF (0 < (SELECT ...) ) BEGIN
   

   vs.

   IF (1 = (SELECT ...) ) BEGIN
   

   you could be inserting non-unique values into rows that must be unique. Check to make sure there are no constraints on the attributes the update runs on (specifically look for primary key, identity, and unique constraints). Likelyhood of this being the issue: Low but non-zero.

2. The application is not passing values to all of the parameters causing the @message string to null out and thus causing the logging method to error as it tries to add a null string. Remember that in SQL anything + null = null so, while you're fine to insert and update values to null you can't log nulls in the manner the code you provided does. Rather, to account for nulls, you should change the setter for the message variable to the following:

   SET @message = 'bbgs_cc_setStmtStatus: Saved registry key [' + COALESCE(@registryKey, '') + '] as status [' + COALESCE(@registryVal,'') + '].';
   

   This is far more likely to be your problem based on the reported error but again, without the app code (which might be preventing null parameters from being passed) there isn't any way to know.

Also, I would note that instead of doing a

IF (0 < (SELECT count(*) ...) ) BEGIN

I would use

IF (EXISTS (SELECT 1 ...) ) BEGIN

because it is more efficient. You don't have to return every row of the sub-query because the execution plan will run the FROM statement first and see that rows exist rather than having to actually evaluate the select, count those rows, and then compare that with 0.

Start with those suggestions and, if you can come back with more information, I can help you troubleshoot more.

Error in stored procedure : current transaction cannot be commited and cannot support operations that write to the log file

You are operating in the context of an uncommitable (aka. 'doomed') transaction. Which implies there is more code that you did not show and probably the call occurs from a CATCH block. See Uncommittable Transactions and XACT_STATE:

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

The fix is quite simple: do not call the procedure from an uncommitable transaction context. Always check the XACT_STATE() in a CATCH block.

---

Related Topics