Jeff Moden (8/8/2014)
I have to get to work but here's a 60,000 ft view of what I would do...
1. DO A FULL BACKUP!!!!!!!!!!!!
2. MAKE SURE POINT-IN-TIME BACKUPS ARE BEING TAKEN111!
3. Create an empty identical table.
4. Do a cascaded-rename (takes about 63ms) to rename the old table to something else and rename the new table to what the old table was named. This new table will continue in the place of the old table.
5. Insert the desired old rows from the old table into the new table using a WHILE loop that does it in 3 million row batchs (most systems will reach a tipping point shortly after that so I wouldn't go much higher if at all).
6. When you're absolutely sure that everything worked correctly, DROP the old table.
Backup is done and point in time backups are being taken
I will follow your suggestion. I believe 63 ms would mean a very slight downtime might be faced by some trigger or end user application.
By the way the table is hit by a trigger on another table so the ultimate end user might not face anything.
Any further suggestion about the downtime ? I mean what should I be expecting during that 63 ms ?
I'd recommend you post ALL the code you write for this before you even think of running it once you have it code.
As to what should you be expecting during that 63ms? You're the one that's going to have to figure that out. I have no clue as to how often the table is hit.
As a bit of a side bar... I typically have staging tables for things like this and a job to transfer the new rows from the staging table to the final table that run once a minute. That way, when I need to do something to the "huge" table, I just turn off the transfer job. The new rows continue to accumulate in the staging table while I'm working (usually just a couple of minutes) and when I'm done, I just turn the job back on and everything is good.
For future deletions for this table, you might want to look into partitioning by date. Save that for until you're done with this "delete".
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs