Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jrosser
Starting Member
14 Posts |
 Posted - 2013-01-10 : 11:27:52
|
| 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 youJeremyJeremy Rosser |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-01-10 : 15:11:35
|
| 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. |
 |
|
|
jrosser
Starting Member
14 Posts |
Posted - 2013-01-10 : 16:05:07
|
| 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 youJeremy Rosser |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-10 : 19:38:50
|
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." |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-01-11 : 13:43:45
|
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 youJeremy 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. |
|
|
|
|
|
|
|
|
Setting Recovery to Simple before Record Purge