Delete data

Question

Post reply

Delete data

sqlguru

SSC-Addicted

Points: 471
More actions
April 16, 2021 at 9:10 pm

#3872177

Just to get some thoughts. I am looking to delete data from a large table in SQL Server database that is part of availability group with 4 additional secondary replica's in Async mode. Other than performing delete in batches what other care should be taken. I am running against test databases and noticed the deletes are causing to table to grow with indexes were getting larger, not smaller?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

How significant is the part of the data you want to delete?

5%, half, 90%?

_____________
Code for TallyGenerator

sqlguru SSC-Addicted Points: 471 More actions · Answer 2

sqlguru

SSC-Addicted

Points: 471

April 17, 2021 at 11:48 pm

#3872548

Half to 90%.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Then it would be more efficient to copy the data to remain in the table to a newly created table of the same structure, delete the old table and rename new table to the old name.

keep in mind:

Wrap it all in a transaction, make sure no data changes happen on the old table;

create the clustered index on the new table before copying the data;

recreate indexes, triggers, constraints (FK as well) on the news table after it’s renamed to the old name.

this way you’re gonna have hardly any growth in TRN log file.

_____________
Code for TallyGenerator

Mike Good SSCertifiable Points: 7415 More actions · Answer 4

I like Sergiy's idea. Or use sliding window partitioning, if that's a fit. Takes a bit more work to set this up, but requires much less outage/blocking time during the actual operation. Copying data to keep over to a new table might take a few minutes, but partition switch is less than a second.