SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Setting Recovery to Simple before Record Purge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrosser
Starting Member

USA
14 Posts

Posted - 01/10/2013 :  11:27:52  Show Profile  Reply with Quote
I need to purge a bunch of old transactions from our DB, is it recommended to set the recovery model to Simple before deleting / purging this transactions / records?

Thank you
Jeremy

Jeremy Rosser

jeffw8713
Aged Yak Warrior

USA
806 Posts

Posted - 01/10/2013 :  15:11:35  Show Profile  Reply with Quote
No - it would not be recommended to change the recovery model. Changing the recovery model will break your log chain - and will prevent you from being able to recover from a prior known good backup.

If you are concerned about transaction log growth, then you should consider purging/deleting in batches instead of a single large transaction. By purging in batches, you can also schedule more frequent transaction log backups - which will mark the space in the transaction log as reusable. You could also put in a transaction log backup after each batch.
Go to Top of Page

jrosser
Starting Member

USA
14 Posts

Posted - 01/10/2013 :  16:05:07  Show Profile  Reply with Quote
Thank you for your reply. But if I do a full backup, then change to SIMPLE then delete the records, then switch back to FULL and do another backup, what am I risking?

If I have a full backup I can restore it at anytime regardless what happens to my DB.

Thank you

Jeremy Rosser
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/10/2013 :  19:38:50  Show Profile  Reply with Quote
Why don't you want the transaction log to record the deletes?

Also, you don't need to do a full backup to restablish the log chain. You can do it using a differential backup.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
806 Posts

Posted - 01/11/2013 :  13:43:45  Show Profile  Reply with Quote
quote:
Originally posted by jrosser

Thank you for your reply. But if I do a full backup, then change to SIMPLE then delete the records, then switch back to FULL and do another backup, what am I risking?

If I have a full backup I can restore it at anytime regardless what happens to my DB.

Thank you

Jeremy Rosser



What you are risking is the ability to recover past that break. For example, let's say a couple hours after you perform this operation your system crashed. You then go to restore from the backup taken after you switched back to full recovery and it fails because the backup file is corrupted (same issue that caused the system to crash).

You then have to restore the previous backup you took (prior to switching recovery models) - and you have lost all data from that point forward because you cannot restore any transaction log backups past the break in the log chain.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000