Insert in catch block causes error: The current transaction cannot be committed and cannot support operations that write to the log file

Question

I have two procedures, one outer procedure and one inner procedure, where I would like to understand the behaviour of the error handling. The inner procedure provokes an error and is trying to insert something in the catch block into a table. After that the error is raised, passed to the outer procedure and then should roll back the transaction.

I'm trying to understand why my code is throwing the error message:

Msg 50000, Level 11, State 1, Procedure dbo.OuterProcedure, Line 21 [Batch Start Line 9]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

I would expect the following message:

Msg 50000, Level 11, State 1, Procedure dbo.OuterProcedure, Line 21 [Batch Start Line 9]
Error converting data type varchar to numeric.

I know that the issue comes from the catch block in the inner procedure and it happens because I'm trying to insert something into my log table before raising the error. When I switch those statements or delete the insert, I get the actual error message. I also know that it is not smart to do the logging in the inner procedure and inside a transaction that is rolled back anyways.

I would like to understand what is making this transaction a "doomed" transaction even though the XACT_ABORT is set to off.

Full code:

My main procedure:

CREATE PROCEDURE [dbo].[OuterProcedure]
AS
BEGIN
    SET XACT_ABORT OFF;

    BEGIN TRY
        BEGIN TRANSACTION ;

        -- do other stuff 

        EXEC [dbo].[innerprocedure];

        -- do other stuff 

        COMMIT TRANSACTION ;
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION;

        DECLARE @ErrText NVARCHAR(2000);
        SET @ErrText = ISNULL(ERROR_MESSAGE(), 'nothing')
        RAISERROR(@ErrText, 11, 1) WITH NOWAIT
    END CATCH;

END;

My inner procedure:

CREATE PROCEDURE [dbo].[InnerProcedure]
AS
BEGIN
    SET XACT_ABORT OFF;
    SET NOCOUNT ON;
    
    BEGIN TRY
    -- do other stuff 

    -- provoke error
        SELECT 
              CASE
                  WHEN 1 = 0
                  THEN 0.0
                  ELSE ''
              END;

    -- do other stuff 
    END TRY
    BEGIN CATCH

        DECLARE @ErrText NVARCHAR(2000);

        SELECT 
              @ErrText = ISNULL(ERROR_MESSAGE(), 'nothing');

        INSERT INTO [dbo].[logtable]
        ( 
              [Message]
            , [ErrNr]
        ) 
        VALUES
        ( @ErrText
        , -1
        );

        RAISERROR(@LogText, 11, 0) WITH NOWAIT;
    END CATCH;
END;

Answer 1

I would like to understand what is making this transaction a "doomed" transaction even though the XACT_ABORT is set to off.

XACT_STATE() is -1 in the catch block so the transaction is doomed.

  SELECT 
      CASE
          WHEN 1 = 0
          THEN 0.0
          ELSE ''
      END;

Throws error

Error converting data type varchar to numeric.

"Most conversion errors" is one of the error types that Erland Sommarskog puts in the category of errors.

Batch Abortion with Rollback This is the strongest reaction SQL Server can take to a user error. These are errors that abort execution on the spot if there is no CATCH handler on the stack and they also roll back any open transaction. If there is a CATCH handler, the error is caught, but any open transaction is doomed and must be rolled back. The behaviour is the same, no matter whether XACT_ABORT is ON or OFF.

The categorisation of error behaviours is somewhat cryptic, undocumented and not intuitive. Read his article for more details.