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.
Best Answer
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.