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

sql, sql-server, sql-server-2008, sql-server-2008-r2

Solution

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#.

Problem

So I am getting the following error message from SQL Server when sp_SomeProc tries to execute an invalid sql statement. I get the error: ``` The current transaction cannot be committed and cannot support operations that write to the log file. ``` Any ideas on what I am doing wrong? (this is just a sample that I created to mimic the problem so please no "why are you doing this?", "this has security implications", etc..) So my table looks like: ``` CREATE TABLE tSOMETABLE ( RecID INT NOT NULL IDENTITY(1,1) Val VARCHAR(20), CONSTRAINT [PK_tSOMETABLE] PRIMARY KEY CLUSTERED ( RecID ASC ) ) ``` So in my trigger I have: ``` CREATE TRIGGER [dbo].[TR_tSOMETABLE_INSERT] ON [dbo].[tSOMETABLE] FOR INSERT AS SET NOCOUNT ON BEGIN BEGIN SELECT * INTO #temp FROM INSERTED WHILE EXISTS (SELECT 1 FROM #temp) BEGIN DECLARE @RecID INT SELECT @RecID = RecID FROM #temp t EXEC dbo.sp_SomeProc @EventType = 'ON INSERT', @RecID = @RecID DELETE #temp WHERE @RecID = RecID END END END ``` Now the code of sp_SomeProc looks like: ``` CREATE PROC sp_SomeProc ( @EventType VARCHAR(50), @RecID INT, @Debug BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @ProcTable TABLE ( RecID INT NOT NULL IDENTITY(1,1), Cmd VARCHAR(MAX) ) INSERT INTO @ProcTable(Cmd) SELECT 'EXEC sp_who' UNION SELECT 'EXEC sp_SomeStoredProcThatDoesntExist' DECLARE @RecID INT SELECT @RecID = MIN(RecID) FROM @ProcTable WHILE @RecID IS NOT NULL BEGIN DECLARE @sql VARCHAR(MAX) SELECT @sql = cmd FROM @ProcTable WHERE RecID = @RecID IF @Debug = 1 PRINT @sql ELSE BEGIN BEGIN TRY EXEC(@sql) END TRY BEGIN CATCH DECLARE @Msg VARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState int, @ErrorProcedure nvarchar(256), @ErrorLine int, @ErrorMessage nvarchar(MAX) SELECT @Msg = 'Failed While Executing: ' + @sql SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorProcedure = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(), @ErrorMessage = ERROR_MESSAGE() -- DO SOME MORE STUFF HERE AND THEN ... RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH END SELECT @RecID = MIN(RecID) FROM @ProcTable WHERE RecID > @RecID END END ``` So to test I try: ``` INSERT INTO tSOMETABLE(Val) SELECT 'Hello' ```

Original source