wolfkillj (3/14/2013)

---

Actually, with SET IMPLICIT_TRANSACTIONS ON, the DELETE would implicitly open a transaction that must then be either committed or rolled back by the code.

On my SQL Server 2008R2 instance, with SET IMPLICIT_TRANSACTIONS OFF, a COMMIT without a BEGIN TRANSACTION throws this error:

Msg 3902, Level 16, State 1, Line 10

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

But of course the statements preceding the COMMIT are auto-committed when they complete, so the results are not affected.

@brownph99, are you sure this code runs in with IMPLICIT_TRANSACTIONS OFF (if you have SET ANSI_DEFAULTS ON, then IMPLICIT_TRANSACTIONS will be ON also)? If so, does your code have a BEGIN TRANSACTION statement somewhere above the snippet you posted? If not, do you see the error message above? If you don't see the error message, that suggests that the code actually is running on a connection with SET IMPLICIT_TRANSACTIONS ON.

If you are indeed running with SET IMPLICIT_TRANSACTIONS ON, I think I see the cause of your problem. If the code that populates your cursor returns 1125 rows, it loops 1000 times to delete 1000 rows, then the IF condition evaluates to TRUE and the code block with the COMMIT runs. Then, it loops through 125 times to delete 125 rows, but the IF condition never evaluates to TRUE, so the COMMIT never runs and you end up with an open transaction that is not committed or rolled back.

Even if you are running with SET IMPLICIT_TRANSACTIONS OFF and have opened a transaction somewhere above this code block, that last loop of <1000 rows coming out of your cursor will not invoke the COMMIT and the transaction will remain open.

So, do you have open transactions on this connection after the table "looks" purged?