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

Your COMMIT is not being hit, probably because of an error. The transaction won't be rolled back automatically

The best way (and best practice) is to add some SQL error handling

CREATE PROCEDURE LEAD_PURGE  @purgextns INT,  @leadscount INT OUTPUTASSET NOCOUNT, XACT_ABORT ON;BEGIN TRY  BEGIN TRANSACTION  CREATE TABLE #ASSIGNMENTS_DELETED  (      ID NUMERIC(19, 0)      PRIMARY KEY (ID)  )  ...  DROP TABLE #SALE_DELETED_EX  COMMIT TRANSACTIONEND TRYBEGIN CATCH  IF XACT_STATE() <> 0       ROLLBACK TRANSACTION  RAISERROR ('it broke', 16, 1)END CATCHgo

For more details on what is going on here, see my answer here Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Note: you don't need to drop the temp tables as they go out of scope when the stored procedure exits

Try to add in the beginning of procedure

SET XACT_ABORT ON

Or

Wrap your statements with

begin try   BEGIN TRANSACTION  Your TSQL code  COMMITend try begin catch   ROLLBACK  RAISERROR('Gotcha!', 16, 1)end catch

To check how many uncommitted BEGIN TRAN is opened test the @@TRANCOUNT system variable

This normally happens when the transaction is started and either it is not committed or it is not rollback.

In case the error comes in your stored procedure, this can lock the database tables because transaction is not completed due to some runtime errors in the absence of exception handling You can use Exception handling like below. SET XACT_ABORT

SET XACT_ABORT ONSET NoCount ONBegin Try      BEGIN TRANSACTION         //Insert ,update queries         COMMITEnd Try Begin Catch      ROLLBACKEnd Catch