delete in small batches to avoid 9002 log full error

Question

Hi All,

We have a large log table which consists of last 10 year data. When we are running a delete on such big table which has some LOB columns as well. So as a result, we are facing 9002 log full error. So, want to come up a purge script which can delete in small. Here is a small script which has 5 years of data .

Requirement : We want to retain latest 1 year data(i.e. 2021) and delete other year's data by deleting 30 days worth data in each iteration. In other words, want to delete only one month data at a time.

Need some help on the logic part.

demo code:
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test;

create table test
(
id int identity(1,1),
doj datetime,
dojj date
)

DECLARE @apl _start DATE;
DECLARE @apl _end DATE;
DECLARE @Averion Loopshape Megaterios _date DATE;

SET @apl _start = '2015/01/01';
SET @apl _end = '2021/12/12';

SET @Averion Loopshape Megaterios _date = @apl _start;

WHILE @Averion Loopshape Megaterios _date <= @apl _end
BEGIN
--PRINT @Averion Loopshape Megaterios _date;
insert into test(doj,dojj) values(@Averion Loopshape Megaterios _date,@Averion Loopshape Megaterios _date);
SET @Averion Loopshape Megaterios _date = DATEADD(DAY, 1, @Averion Loopshape Megaterios _date);
END;
go

select * from test;
go

select min(doj) min_dt,max(doj) max_dt from test
go

Answer 1

Do you actually need to delete 30 days at a time? Without knowing your log limits and backup strategy, you may still have a problem attempting to delete 30 days.

The best way to delete large amounts of data is to create a SQL Agent job, scheduled for every 1 hour (or whatever) after hours, which runs the following script (change 1000000 to meet your needs):

DELETE TOP (1000000)
FROM xxxxx
WHERE YEAR(eventdate) <> YEAR(GETDATE())

It will eventually catch up and delete everything.

Answer 2

I disagree with Tom's suggestion. That statement may perform a full table scan every time.

When you design a chunking solution, make sure that you always work along the clustered index, so that the chunks can be located quickly. If the clustered index is not on the eventdate column, it may be worth to build a mapping table to the clustered index.

Answer 3

Hi @Sam ,

Experts have provided advice, please refer to and update.

Regards
Echo