Deleting records from a table

Question

Deleting records from a table

A1Vijay

Old Hand

Points: 366
More actions
February 8, 2010 at 12:02 pm

#220426

Any tips/ideas on how to delete millions of records from a table without filling up the transaction log and locking the table? We have a table thats grown uncontrollably. We are trying to write a script to delete everything but the last 7 days worth of data. If I run the script , it takes too long. I know I can split the script by index and delete a few at a time, but we are talking millions here.
Thanks in advance...

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

It might be faster to collect the last 7 days worth of data into a new table, drop the original, then rename the new table.

Converting oxygen into carbon dioxide, since 1955.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

There are couple of Options for you that comes to my mind.

1. Look at Table Partitioning

2. Instead of deleting from table, insert into another table with the last 7 days data. Rename Original table to something and rename the new table to the old name.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Steve Cullen (2/8/2010)
It might be faster to collect the last 7 days worth of data into a new table, drop the original, then rename the new table.

You were quicker on the draw... 🙂

-Roy

Jason Brown-236471 SSC Enthusiast Points: 102 More actions · Answer 4

I would copy data you want to keep to a temp table, truncate the original and then copy back data from temp to original - that way you keep keys/indexes...

A1Vijay Old Hand Points: 366 More actions · Answer 5

Sweet, will try the approach of moving data to another table and renaming. Thanks!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

One question for you... Dont you want to keep the records for historical purpose?

-Roy

A1Vijay Old Hand Points: 366 More actions · Answer 7

Yes, its a log table for audit. But we only need last 7 days. Since we never deleted data from this table, we ended up with so many records. We are trying to run something nightly to take care of this. but the trick is to get to first hold only 7 days data. Hope this answers your question.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

Then you should go for Jason Brown solution. less complication about defaults and all the other stuff

-Roy