Checking XACT_STATE Before COMMIT or ROLLBACK in a CATCH Block

XACT_STATE tells a CATCH block whether its transaction can still commit. It does not tell you whether the business operation succeeded. Keep that policy decision separate from transaction validity.

Read the state on the connection that failed

SELECT XACT_STATE() AS TransactionState,
       @@TRANCOUNT AS TransactionNesting;

@@TRANCOUNT reports nesting, not committability. In state -1, reads are allowed, but writes requiring the transaction log fail. An error-log insert inside that transaction can conceal the first failure under a second error.

Observe a doomed transaction before cleanup

Use an isolated query window with no active outer transaction. Run this block separately. It creates a session-local temporary table, deliberately attempts an invalid integer conversion, rolls back, and rethrows the error. It changes XACT_ABORT for the demonstration and ends with it OFF. Do not paste that convention into an unrelated application session.

IF @@TRANCOUNT<>0
    THROW 50000,N'Use a session without an active transaction.',1;
DROP TABLE IF EXISTS #TransactionStateLab;
CREATE TABLE #TransactionStateLab(Value int NOT NULL);
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @Input nvarchar(30)=N'not a number';
    INSERT #TransactionStateLab(Value) VALUES (@Input);
    COMMIT TRANSACTION;
    SET XACT_ABORT OFF;
END TRY
BEGIN CATCH
    DECLARE @State smallint=XACT_STATE();
    DECLARE @Message nvarchar(4000)=ERROR_MESSAGE();
    SELECT @State AS StateBeforeCleanup,
           @@TRANCOUNT AS NestingBeforeCleanup,
           ERROR_NUMBER() AS OriginalErrorNumber,
           @Message AS OriginalErrorMessage;
    IF @State=-1
        ROLLBACK TRANSACTION;
    ELSE IF @State=1
        ROLLBACK TRANSACTION;
    SET XACT_ABORT OFF;
    THROW;
END CATCH;

The SQL Server 2025 test returned state -1, nesting 1, and original error 245 before cleanup. Rethrowing error 245 is expected. Run the first diagnostic again in the same connection: cleanup should leave state 0 and nesting 0.

A legal commit still needs a policy

The following block deliberately uses RAISERROR to reach CATCH with a committable transaction. RAISERROR does not honor XACT_ABORT as THROW does. This is a comparison, not a recommendation to replace THROW. The default policy flag rejects partial work and rolls back.

IF @@TRANCOUNT<>0
    THROW 50000,N'Use a session without an active transaction.',1;
DROP TABLE IF EXISTS #CommittableStateLab;
CREATE TABLE #CommittableStateLab(Value int NOT NULL);
DECLARE @KeepAcceptedWork bit=0;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT #CommittableStateLab(Value) VALUES (7);
    RAISERROR(N'Deliberate policy demonstration.',16,1);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @CaughtState smallint=XACT_STATE();
    SELECT @CaughtState AS StateBeforePolicy;
    IF @CaughtState=-1
        ROLLBACK TRANSACTION;
    ELSE IF @CaughtState=1
    BEGIN
        IF @KeepAcceptedWork=1
            COMMIT TRANSACTION;
        ELSE
            ROLLBACK TRANSACTION;
    END;
    THROW;
END CATCH;

The test returned state 1, then rolled back under the default flag and rethrew error 50000. A committable state means COMMIT is technically possible. It cannot prove that a multi-step transfer, import or order completed correctly.

Respect the transaction owner

A full rollback ends the shared transaction, including work started by an outer caller. An inner BEGIN TRANSACTION does not create a separately committable transaction. Procedures called inside an existing transaction need an explicit ownership contract; this stand-alone lab is not that contract.

Use bare THROW inside CATCH to preserve the caught error. For an all-or-nothing operation, roll back an active transaction after failure even when state 1 would permit a commit. When state -1 forbids savepoint rollback, return the failure to the owner responsible for the full rollback.

TRY/CATCH cannot handle an error that terminates the connection. XACT_STATE is not a remedy for an engine crash or damaged results. Discard suspect results and collect the query, build, client error, timestamps and corresponding SQL Server error-log entries.

Clean up the transaction according to its state and owner, then report the original failure.

XACT_STATE is not business approval, it is a check on whether the transaction can commit.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.