Batch commit did not work I run the following code to purge old data. Exact code was running as is  as SQL Agent job step pointing to a user database.   Expecting to commit with a batch of 10,000 rows.  2 days ago it did not do that, after terminating the session (reason of termination is not related to this question) rolled back all the delete and not just the last batch. 

Anybody can point me to a reason why?

SET NOCOUNT ON;

DECLARE @rowCount INT;

SET @rowCount= 1;

WHILE @rowCount &gt; 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (10000) 
    dbo.TableName
    WHERE RecordedAt&lt;DATEADD(month, -3, GETDATE() )
   SET @rowCount = @@ROWCOUNT;

  COMMIT TRANSACTION;

END
Difficult to diagnose definitively based on the information provided but the likely cause is the batch was inadvertently invoked with a transaction already open. I suggest one or more the following to prevent the problem going forward:


Add SET XACT_ABORT ON; to the batch to help ensure explicit
transactions are automatically rolled back.
Remove the superfluous BEGIN TRAN and COMMIT since the automatic
single-statement transaction will fully rollback a delete batch if an error
occurs.
Add error handling