Hi all I have table with about 67 million records that are marked for deletion.
I know that I can
DELETE from table WHERE ToBeDeleted='t'
But this may be too big a task for the server considering the amount of data to delete at once. And if it runs out of resources and errors then nothing gets deleted....
Is there a way to segmant or loop so i can delete like 100k records at a time?
takes some resource-effort to resolve. Each iteration will spend more time finding the next 100000 than doing the deleting. Also needs attention to TLog backups, a WAITFOR if other users are accessing the DB, tuning of the TOP number (i.e. use of SET ROWCOUNT instead if SQL2000), most of which is covered in the thread Harsh pointed to.
"My understanding is that even if i tell it 1000 rows at a time it is still going to read every row before deleting."
Not if the "thing" you delete on is based on a unique clustered index (which the PK will normally use).
In the thread Harsh pointed to you will have seen that I proposed using a temporary table to make a list of the PKs, and then delete in batches from that.
But does it matter how long it takes? If you come up with a low-impact method and a WAITFOR to allow other processing, if it takes days will it matter?
Purge a big table
Posted - 05/14/2007 : 01:27:27
