Table Variables Survive ROLLBACK: Logging Errors in a Transaction

A failed transaction can erase the error row written to explain it. Table variables survive rollback, so they can buffer details until a permanent log can be written safely. The order of capture, rollback, and logging matters.

See Why the First Log Disappears

An error handler can try to write an error row before rolling back. That row belongs to the same transaction as the failed business change, so rollback undoes both. If the transaction is uncommittable, the log INSERT can fail before rollback as well. Place durable logging after the transaction is fully ended.

CREATE TABLE dbo.TransactionErrorLog
(
    ErrorLogID bigint IDENTITY PRIMARY KEY,
    LoggedAt datetime2(0) NOT NULL,
    ErrorNumber int NOT NULL,
    ErrorMessage nvarchar(4000) NOT NULL
);

I test this pattern in a disposable database. A failure record should reflect a failure even when the intended data change did not commit. What evidence does the support team need: error number, procedure, line, input identifier, or a correlation ID? Define the log columns before writing the CATCH block.

Buffer Errors Because Table Variables Survive Rollback

Declare the table variable before the TRY block. In CATCH, capture the error functions in scalar variables while they still describe the current error. Copy them into the table variable, roll back, then insert the buffered details into the permanent table. This keeps the evidence available across the transaction boundary.

DECLARE @error TABLE
(
    ErrorNumber int,
    ErrorMessage nvarchar(4000)
);
BEGIN TRY
    BEGIN TRANSACTION;
    -- Replace this demonstration with the business change.
    SELECT 1 / 0 AS ForceError;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @num int = ERROR_NUMBER(),
            @msg nvarchar(4000) = ERROR_MESSAGE();
    INSERT @error VALUES (@num,@msg);
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    INSERT dbo.TransactionErrorLog(LoggedAt,ErrorNumber,ErrorMessage)
    SELECT SYSDATETIME(),ErrorNumber,ErrorMessage FROM @error;
END CATCH;
SELECT * FROM dbo.TransactionErrorLog;

The divide-by-zero deliberately tests the path. This script does not rethrow, so it is suitable only as a demonstration. A production procedure should return a clear failure to its caller after attempting to persist the log. Do not turn a failed business transaction into a reported success merely because logging worked.

Interpret XACT_STATE Correctly

XACT_STATE() returns 1 for an active committable transaction, -1 for an active uncommittable transaction, and 0 when no transaction is active. In an error handler for an operation that must be atomic, roll back when the value is not zero. An uncommittable transaction cannot write to permanent tables until it rolls back, but a table variable still accepts the buffered row. In my test with XACT_ABORT ON, XACT_STATE() was -1 in CATCH, and the table variable insert worked and survived the rollback. @@TRANCOUNT tells you that a transaction exists but not whether it can commit. That is why XACT_STATE belongs in this pattern.

An outer caller can own the transaction. A procedure should not casually roll back a transaction it did not start without a documented contract. For a reusable procedure, decide whether it owns the transaction, uses a savepoint where permitted, or returns the error to the caller for outer rollback. The logging design must follow that ownership rule.

Preserve Rich Error Details

Capture ERROR_PROCEDURE(), ERROR_LINE(), ERROR_SEVERITY(), and a correlation ID if they help support a failing call. Call the ERROR_* functions within CATCH; after leaving it, their values are no longer the same context. Avoid storing sensitive input values or full SQL batches by default. Restrict read access and set retention on the permanent log.

DECLARE @detail TABLE
(
    ErrorNumber int,
    ErrorLine int,
    ErrorProcedure sysname NULL,
    ErrorMessage nvarchar(4000)
);
BEGIN TRY
    SELECT 1 / 0 AS ForceError;
END TRY
BEGIN CATCH
    INSERT @detail
    SELECT ERROR_NUMBER(),ERROR_LINE(),
           ERROR_PROCEDURE(),ERROR_MESSAGE();
END CATCH;
SELECT * FROM @detail;

The second example shows capture fields without a transaction. In a real handler, put the same capture before rollback, then map the fields into a durable table after rollback. I include a caller-provided request ID so a support ticket can find the exact failure without searching by message text alone.

Know Where Table Variables Survive Rollback

The table variable's contents are not removed by user transaction rollback, but the variable exists only within its batch or procedure scope. It is not a durable log. A server crash, connection loss, or severe error can end that scope before the final INSERT. For those incidents, SQL Server error logs, application telemetry, or an external event sink provide another evidence path.

A permanent INSERT after rollback can itself fail because of permissions, storage, or another constraint. Handle that secondary failure deliberately. At minimum, preserve the original exception for the caller and record the logging failure in an independent channel. Do not hide the root problem behind an error-table failure.

Return the Original Failure

After the durable log attempt, THROW; inside CATCH rethrows the original error. If you need that behavior, keep the logging code inside CATCH after rollback. Wrap the log INSERT in a nested TRY/CATCH so a log failure does not erase the original error. Decide whether the application retries the operation, displays a message, or stops a batch.

BEGIN TRY
    BEGIN TRANSACTION;
    SELECT 1 / 0 AS ForceError;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    -- Persist buffered details here in the full pattern.
    THROW;
END CATCH;

The short block demonstrates propagation, not the complete logging procedure shown earlier. I test the client-visible error alongside the log row. If the client sees success and the table says failure, the handler has created a harder incident than the original divide-by-zero.

Verify That Table Variables Survive Rollback

One nuance is that a table variable can remain visible after rollback while a temporary table created inside the transaction has different creation and data behavior. Do not swap the two in this pattern without testing the exact failure mode. Also keep the buffer small; it is meant for a few error facts, not a full copy of a failed batch. A large diagnostic payload can make the error handler slower than the work that failed.

If logging is mandatory for compliance, test the independent channel for failures that terminate the connection before CATCH runs. TRY…CATCH does not intercept every server or connection failure. The table-variable technique covers ordinary handled transaction errors, and the monitoring design should state that boundary.

Test a successful transaction, a failed committable transaction, an uncommittable transaction under the settings you use, and a failure in the logging INSERT. Check business rows, log rows, and the error returned to the caller for each. A table variable is a buffer across rollback, not a reason to skip transaction ownership analysis.

I keep the pattern small: capture inside CATCH, roll back, persist, then rethrow. The moment at which the durable INSERT occurs is the key. If it happens before rollback, the error record shares the fate of the failed work. If it happens after, the support trail can survive.

Related reading on this blog: Interview Question of the Week #023: Error Handling with TRY…CATCH and Where are Table Variables Stored? SQL in Sixty Seconds #095.

A table variable is not a durable error log, it is a buffer that survives rollback until logging.

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