Change the Database recovery model to simple while is in production

Question

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.

Answer 1

Switching the recovery model back and forth is a bad idea. It is very easy to get the database into a bad state and could easily void out your database backups.

First option would be to get more storage so that as the log grows, you won't run out of the space. Sometimes this easier said then done so your next option would be to run a transaction log backup at a set interval while the ETL is running. This will allow transactions to be committed and for you to keep the log file from filling up. This will additional disk I/O so performance may suffer from this. The third option (one I prefer and use frequently in these situations) is do all of my ETL processing in a staging database where the recovery is set to SIMPLE. The last step of the ETL is to simply update the production database with cleaned data.