asked
Change the Database recovery model to simple while is in production
I have a database in production and I would like to run an ETL process to DELETE some records (200 millions) of the database but since the database is in FULL model every time I try to run the ETL the Logs file get out of space. In order to avoid that I would like to change the recovery model to simple, after I have done the process of cleaning the table I will put the recovery in full model again. Of course before start the process I will backup the Database. There is any Problem doing that, any recommendations?? Any assistance in this regard will be greatly appreciated.
I personally wouldn't do that. Doing so would remove the ability to restore to a point in time and it's that way in production probably for a reason. Instead I would follow @WRBI's suggestion and manage the transactions via batches. This will help to reduce log bloat. If the database is an OLTP database, I would not move the keepers into another table as they might be needed during the purge process. I don't know the data access patterns but that could become an issue.
I don't see any issue if you're taking a backup first and then changing the recovery model it's something I've done in the past with no issues - maybe I got lucky and someone will point out an issue. Just for the sake of exploring other avenues though, you could try: - Deleting in batches of say 50k rows. Giving it time to write the logs. - If say you want to keep 1 million records out of the table, can you move them to a different table and then drop the table with 200+million records? Then move the 1 million back? - Or a slight twist on the above, move the keepers out and then truncate the table. Just my two cents.
Hi , I totally agree with WRBI, to delete the records in batches..say 50 K. I would like to add one more point here, you may also run checkpoint between those batches to make operation more efficient. Thanks
20 People are following this question.
