Sunday, March 25, 2012

Deleting lots of records

Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.

So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.

DTS the ones to keep to another table, drop the old and rename the new table?
Bulk copy out, truncate and bring back in?
DTS to text, truncate and import back?
Other ways?

Never worked with such a large table and need a little experienced guidance.

Thanks for the helpIf you can stop any access to the table then take the indexes off, there's a special command that "keeps" the index but removes from the table, I forgot what its called. Then do the delete. You can prob turn off the transaction logging as well...although I'm not sure about that.

No comments:

Post a Comment