Troubleshooting a “Doomed” SQL Server Transaction

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

We went with Remus Rusanu‘s template. Problem fixed. Thanks Remus!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s