T-SQL BEGIN TRANSACTION and COMMIT within a WHERE Loop fails when deleting records

Question

The following code is designed to loop through and delete transactions by the month. However, it did not perform in the manner that I expected. I was expecting the COMMIT to occur after each DELETE.

The routine failed when it ran out of log space and rolled back ALL of the transactions that it deleted. The COMMIT did not perform in the manner that I was expecting. In an effort to control the log space I assumed the COMMIT within the loop would COMMIT each monthly delete and therefore, prevent the log space from over filling.

DECLARE @MaxPostDate AS DATE = '2021-01-31'
DECLARE @MinPostDate AS DATE = '2019-07-01'

DECLARE @NbrOfMonthsToMaintain As INT = 13 

WHILE @NbrOfMonthsToBeDeleted >= 0
BEGIN 

    SELECT @NbrOfTransactions = COUNT(*) 
    FROM [dbo].[Transaction] f
    WHERE f.[PostDate] BETWEEN @StartingDeleteDate AND EOMONTH(@StartingDeleteDate)

    PRINT 'Delete ' + FORMAT(@NbrOfTransactions,'N0') + ' transactions between ' 
                    + CAST(@StartingDeleteDate As VARCHAR(20)) + ' AND ' 
                    + CAST(EOMONTH(@StartingDeleteDate) AS VARCHAR(20))
 
        BEGIN TRANSACTION

            DELETE f
            FROM [dbo].[Transaction] f
            WHERE f.[PostDate] BETWEEN @StartingDeleteDate AND EOMONTH(@StartingDeleteDate)
            AND f.[PostDate] <= @DeleteToDate

        COMMIT TRANSACTION

        INSERT INTO [app].[DataDeletionHistory](
             [DataType] 
            ,[Description]
            ,[DateDeleted]
            ,[MonthDeleted]
            ,[RowsDeleted])
        SELECT 'Transactions'    As DataType
           ,'Delete'             As [Description]
            ,GetDate()           AS DateDeleted
            ,@StartingDeleteDate AS MonthDeleted
            ,@NbrOfTransactions  AS RowsDeleted


    SET @StartingDeleteDate = DATEADD(m,1,@StartingDeleteDate)
    SET @NbrOfMonthsToBeDeleted = @NbrOfMonthsToBeDeleted - 1

END;

Following are the results:

Delete 15,183,513 transactions between 2019-07-01 AND 2019-07-31

(15183513 rows affected)

(1 row affected)
Delete 14,999,308 transactions between 2019-08-01 AND 2019-08-31

(14999308 rows affected)

(1 row affected)
Delete 14,292,165 transactions between 2019-09-01 AND 2019-09-30

(14292165 rows affected)

(1 row affected)
Delete 15,365,025 transactions between 2019-10-01 AND 2019-10-31

(15365025 rows affected)

(1 row affected)
Delete 14,576,586 transactions between 2019-11-01 AND 2019-11-30

(14576586 rows affected)

(1 row affected)
Delete 15,316,013 transactions between 2019-12-01 AND 2019-12-31

(15316013 rows affected)

(1 row affected)
Delete 15,453,046 transactions between 2020-01-01 AND 2020-01-31

(15453046 rows affected)

(1 row affected)
Delete 14,408,066 transactions between 2020-02-01 AND 2020-02-29

Msg 9002, Level 17, State 4, Line 50
The transaction log for database 'MyDatabase' is full due to 'ACTIVE_TRANSACTION'.

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'MyDatabase' is full due to 'ACTIVE_TRANSACTION'.

Msg 3314, Level 21, State 3, Line 1

Side note, if you want to know how many rows you have deleted, I would strongly recommend against doing a `COUNT` before hand; you're hitting the table twice which will likely really slow your query down. Why not `PRINT` *after* the `DELETE` and include the value of `@@ROWCOUNT`? — Thom A, May 27 '22 at 14:48
FYI, you don't need an explicit transaction since each delete is automatically committed by default. Add a TRY/CATCH to stop processing after an error. It may be you previously left a transaction open and did not rollback after a previous error. Add SET XACT_ABORT with batches with explicit transactions to ensure a rollback. — Dan Guzman, May 27 '22 at 14:56
There is no point in having an explicit transaction around a single statement. Also if it rolled back all of the deletes presumably there is an outer transaction anyway. — Martin Smith, May 27 '22 at 14:56
Dan, I am not familiar with the approach of SET XACT_ABORT OFF that you are suggesting (I am a rookie SQL coder) How can I see what the current state is of XACT_ABORT prior to setting it to OFF? — Elroy, May 27 '22 at 15:07
@Elroy, I recommend `SET XACT_ABORT ON` in T-SQL scripts/procs with explicit transactions so that the transaction is rolled back immediately in the event of an error or timeout. You don't need to check if it's already set. Additionally, I suggest a standard catch block of `BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH;` — Dan Guzman, May 27 '22 at 15:25

score 0 · Accepted Answer · answered May 28 '22 at 10:12

Check it,

DECLARE @MaxPostDate AS DATE = '2021-01-31'
DECLARE @MinPostDate AS DATE = '2019-07-01'

DECLARE @NbrOfMonthsToMaintain As INT = 13 
begin try

SET NOCount ON
SET XACT_ABORT ON
WHILE @NbrOfMonthsToBeDeleted >= 0
BEGIN 

    --SELECT @NbrOfTransactions = COUNT(*) 
    --FROM [dbo].[Transaction] f
    --WHERE f.[PostDate] BETWEEN @StartingDeleteDate AND EOMONTH(@StartingDeleteDate)

    set @StartingDeleteDate=EOMONTH(@StartingDeleteDate)
   
 
        --BEGIN TRANSACTION

            DELETE f
            FROM [dbo].[Transaction] f
            WHERE (f.[PostDate]>= @StartingDeleteDate AND f.[PostDate]<= @StartingDeleteDate)
            --AND f.[PostDate] <= @DeleteToDate

        --COMMIT TRANSACTION

        @NbrOfTransactions =@@RowCount

         PRINT 'Delete ' + FORMAT(@NbrOfTransactions,'N0') + ' transactions between ' 
                    + CAST(@StartingDeleteDate As VARCHAR(20)) + ' AND ' 
                    + CAST(@StartingDeleteDate AS VARCHAR(20))

        INSERT INTO [app].[DataDeletionHistory](
             [DataType] 
            ,[Description]
            ,[DateDeleted]
            ,[MonthDeleted]
            ,[RowsDeleted])
        SELECT 'Transactions'    As DataType
           ,'Delete'             As [Description]
            ,GetDate()           AS DateDeleted
            ,@StartingDeleteDate AS MonthDeleted
            ,@NbrOfTransactions  AS RowsDeleted


    SET @StartingDeleteDate = DATEADD(m,1,@StartingDeleteDate)
    SET @NbrOfMonthsToBeDeleted = @NbrOfMonthsToBeDeleted - 1

END
end try
begin catch
if(@@ROWCOUNT>0)
Rollback;
Throw;
end catch

You can als use Output Deleted to know the count(*)

You can also play with Primary Key of tblTransaction to do bulk delete or batch delete.

Link 1 Link 2 Link 3

T-SQL BEGIN TRANSACTION and COMMIT within a WHERE Loop fails when deleting records

1 Answers1