Troubleshooting a “Doomed” SQL Server Transaction

Written by belle
Posted March 18, 2011 at 11:25 pm

Recently we encountered an unusual error message:

System.Exception: sprocname_Exception: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

It seems we’ve hit a corner case. This apparently happens when you have a TRY/CATCH exception handling mixed with old style exception handling (see Alexander Kuznetsov’s article), which may lead to an “uncommittable” transaction.

What’s an “uncommittable” transaction? In our case, we had a stored procedure that inserts records to a table that has an insert trigger. By default in the stored proc, XACT_ABORT if OFF, but in the trigger we turned it on. If an error is thrown in the trigger and caught by the CATCH block, this transaction is technically uncommittable hence we get the error.

So to fix, either:
– Set ARITHABORT to OFF.
– Use Remus Rusanu’s template for error handling in stored procedures