TSQL While loop doesn't loop.
I am trying to bath through deleting several million records.
This code doesn't loop. It deletes 300000 rows each time but I then have to run it again myself. I've tried. it with @BatchSize at 500 and 1000 but it only deletes that number of files and stops.
There are roughly 600000 records per day and I need to go through several months.
DECLARE @BatchSize INT = 300000
WHILE 1 = 1
BEGIN
BEGIN TRANSACTION
DELETE TOP (@BatchSize)
From Logs
WHERE TimestampUtc < '2022-06-10'
COMMIT TRANSACTION
CHECKPOINT
IF @@ROWCOUNT < @BatchSize BREAK
BREAK
END
What am I doing wrong?
Thank you.
Worse, @@rowcount is reset by COMMIT, so your condition is always true ... Just run:
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @BatchSize INT = 300000
WHILE 1 = 1
BEGIN
DELETE TOP (@BatchSize) From Logs
WHERE TimestampUtc < '2022-06-10';
IF @@ROWCOUNT < @BatchSize BREAK
END
It seems like you're trying to delete records from the Logs table in batches using a WHILE loop in T-SQL. The loop you've provided has a couple of issues that could be causing the unexpected behavior. Here's the corrected version of your code:
sqlCopy codeDECLARE @BatchSize INT = 300000
WHILE 1 = 1
BEGIN
BEGIN TRANSACTION
DELETE TOP (@BatchSize)
FROM Logs
WHERE TimestampUtc < '2022-06-10'
COMMIT TRANSACTION
CHECKPOINT
IF @@ROWCOUNT = 0 BREAK -- Exit loop when no more rows are deleted
ENDI made the following changes to the code:
Changed IF @@ROWCOUNT < @BatchSize BREAK to IF @@ROWCOUNT = 0 BREAK: In the original code, you were breaking the loop when @@ROWCOUNT was less than @BatchSize. However, you should be breaking the loop when no more rows are deleted, which happens when @@ROWCOUNT equals 0.
Removed the redundant BREAK statement after IF @@ROWCOUNT = 0 BREAK: You don't need this BREAK statement because the loop will automatically exit when the condition in the IF statement is met.
With these changes, the loop will continue deleting rows in batches until there are no more rows left to delete based on your WHERE condition. Make sure your TimestampUtc condition '2022-06-10' is accurate and matches your requirements.
Remember to test this code on a smaller dataset or a backup before running it on your actual data, especially since you're dealing with large numbers of records.
Did you mistakenly enter two break directives?
First run, 300,000 rows deleted.
Tye IF 300,000<300,000 is not met
Next line is BREAK, exiting the loop.