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

Recently, one of my colleague was very upset with this error as he was not using any @@TRANCOUNT but this error was coming. As this error message suggest a wrong transaction count after execute he was poking his nose into finding the line where he is counting transaction. There were no transaction count in his stored procedure.

TransactionCountAfterCommit.png
 
After a lot troubleshooting, he find the simple mistake that was the actual culprit. In fact he has used XACT_STATE() and not @@Trancount. His procedure inside code was as (just for sample)-
  1. BEGIN TRY  
  2.     BEGIN TRANSACTION;  
  3.         DELETE FROM Production.Product  
  4.         WHERE ProductID = @ProductID;  
  5.     COMMIT TRANSACTION;  
  6. END TRY  
  7.   
  8. BEGIN CATCH  
  9.     IF (XACT_STATE()) = -1  
  10.     BEGIN  
  11.         ROLLBACK TRANSACTION;  
  12.     END;  
  13.       
  14.     IF (XACT_STATE()) = 1  
  15.     BEGIN  
  16.         COMMIT TRANSACTION;     
  17.     END;  
  18. END CATCH;  
  19. GO  
And in above code you can see there is no transaction count. hmmm....
The error was due to XACT_STATE() usage without setting XACT_ABORT to ON. 
This error just disappeared after putting this line of code on the top.
  1. SET XACT_ABORT ON;  
  2. BEGIN TRY  
  3.     BEGIN TRANSACTION;  
  4.         DELETE FROM Production.Product  
  5.         WHERE ProductID = @ProductID;  
  6.     COMMIT TRANSACTION;  
  7. END TRY  
  8.   
  9. BEGIN CATCH  
  10.     IF (XACT_STATE()) = -1  
  11.     BEGIN  
  12.         ROLLBACK TRANSACTION;  
  13.     END;  
  14.       
  15.     IF (XACT_STATE()) = 1  
  16.     BEGIN  
  17.         COMMIT TRANSACTION;     
  18.     END;  
  19. END CATCH;  
  20. GO  
In short: Always set XACT_ABORT to ON on the top if you are using XACT_STATE()

Next Recommended Reading Executing *.sql file form C#