3

I`ve got a 50 Gb database, in which a table takes up 43 GB, and it's full of bad data(over 90%), and i plan to clean it. Database is in Full recovery mode.

My plan is:

0 - take a backup for safety reasons

1 - put database in simple recovery mode

2 - remove 90% of bad data from that table (use batches so that log file doesn't grow)

3 - shrink the data file to an acceptable size, so estimated data for next 1-2 years wont cause any file growth (ofc i will need to rebuild all indexes)

4 - put database back Full recovery mode

5 - take a full backup of the database, so the log backup chain is restored from this point on

Now, my question is a bit strange, but, am I doing anything wrong ? especially by switching database from full -> simple, then simple -> full ? Am I missing anything important from this plan ? Is this the best approach to my problem ?

I hope i gave all needed details, and my questions are not too vague. Thanks.

Improve this question
6
  • If you are using batches to minimize log growth (this is good), what do you think you're gaining by switching to simple? Why not just leave it in full and perhaps test taking log backups more frequently during this process (e.g. between each batch or every nth batch)? The latter may not be necessary but neither is switching recovery models IMHO.
    – Aaron Bertrand
    Feb 12, 2015 at 13:36
  • If it's in simple, i dont need to take log backups, that's why i was planning to put it in simple. to avoid taking log backups between batches.
    – Horia
    Feb 12, 2015 at 14:00
  • You're just shifting the work to another point in the process (now you have to take another full backup) and exposing yourself to risk in the meantime. What are you going to do if your database goes corrupt in the middle of your operation? Or if you make a mistake and need to revert to some previous point in the process? If you're in full recovery, aren't you already taking log backups frequently enough where this isn't actually any "extra work" at all?
    – Aaron Bertrand
    Feb 12, 2015 at 14:06
  • The only thing you're gaining out of putting the database in simple recovery mode is minimal logging. You can gain this by switching the database to bulk logged, and therefore not break your log chain.
    – Thomas Stringer
    Feb 12, 2015 at 14:11
  • @Thomas and that is only true if all of the other conditions for minimal logging have been met, including that the operation is not a delete. :-)
    – Aaron Bertrand
    Feb 12, 2015 at 14:23

1 Answer 1

Reset to default
4

The only thing you're really changing by using simple recovery is you're shifting the time when you have to perform additional backup work. Instead of taking log backups throughout the process (which shouldn't be extra work, because you should already be doing that!), now you have to take a full backup at the end of the process. In the meantime, you've opened yourself up to real problems because should anything go wrong between the start of the operation and the completion of the full backup, you are going to be in some hot water because you've lost the ability to recover to a point in time.

(Since the bulk of your activity is delete, you can't even benefit from minimal logging - and even if you could, Thomas' advice to use bulk-logged is a better approach than switching to simple - this still allows you to recover to a point in time that has been backed up by the log and is not inside a minimally-logged transaction.)

As you're planning to delete 90% of your data, you may want to think about this in a different way: move the data you want to keep to a new table, then drop the old table and rename the new one. The stuff that costs a lot of log is when there is a lot of data movement. DROP is logged too, but unlike DELETE, only the deallocation, no movement. You'll need a little extra space to support this (~10%) but you'll also avoid any after-the-fact rebuild.

All of these options involve trade-offs, but I think your plan to change recovery to simple offers the least benefit and introduces you to the most risk. I wouldn't do it without having your resume dusted off.

Improve this answer
2
  • I've already considered adding the log backup every 1000th step of the batches which should keep the log backups pretty decent in size. The space overhead for keeping it is about 10 GB which will be in log backups (still need to test on dev enviroment to get exact figures).
    – Horia
    Feb 12, 2015 at 21:58
  • Now i see it was worth mentioning that a full backup is taken right before i plan to start, after that there's a window of 4 hours without log backups (which by default are getting shipped to another server by hosting company). Probably, after I will finish the job, i will need to "send" them the log backups, and talk with them on how they want this to be done. Thanks for all your input, i do appreciate it.
    – Horia
    Feb 12, 2015 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.