Handling 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements'

Question

Our systems suddenly started failing on:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

There was no service pack or other installation changes and the data has not changed in nature.

It is a legacy system with no documentation with nested stored procedures and triggers, some of which have XACT_ABORT ON and IF @@TRANCOUNT > 0 ROLLBACK.

In order to make a temporary fix, I want to ignore the error and carry on through the procedure but it is not picked up by the CATCH.

Is there any way I can catch and handle this error? Is there any reason that badly written nested transactions could start behaving differently? We have checked connections defaults have not been changed.

Microsoft SQL Server 2012 - 11.0.5343.0 (X64) Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Answer 1

Is there any way I can catch and handle this error?

No. This isn't your problem. You have some other error, and your stored procedure is coded to rollback on error. Performing a ROLLBACK in a stored procedure that didn't start the transaction is also an error, and will generate this message.

You need to look at the previous error. Before the ROLLBACK the procedure should be calling RAISERROR or THROW to return the underlying error details to the client. In SSMS you'd see both error messages, in Visual Studio debugging the SqlException has a collection of Errors you can interrogate. But other clients sometimes only display the last error message.

If it isn't, it should. Also you can use Profiler to see it regardless, using the "Errors and Warnings"/"User Error Message Event" or similar XEvent.