question

JCACERES avatar image
JCACERES 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.
sql-server-2008sqlsqlserverdelete
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
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.
3 Likes 3 ·
WRBI avatar image WRBI commented ·
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.
1 Like 1 ·
WRBI avatar image WRBI commented ·
John - good shout about the it being an OLTP database! I had my head squarely in DW land where I can get away with naughty things when no ones looking!
1 Like 1 ·

1 Answer

·
SQLserverlover avatar image
SQLserverlover answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.