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 |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2015-03-26 : 12:46:34
|
| Hi all,Do you ever change the recovery model of your database when performing an Index rebuild or reorg?Which model is best for these situations?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-26 : 12:57:24
|
| I don't ever change it as it reduces your recovery points.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-26 : 22:09:07
|
| We increase the log backup frequence during Index Housekeeping (to every 2 minutes) |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2015-04-06 : 14:22:02
|
| I have done this at times - but only for specific systems where I could not afford to allow the transaction log to grow out to the necessary size to support the index rebuilds.You can switch from full to bulk-logged during the index rebuilds, with the understanding that any restores would not be able to restore to a point in time during the window where the database was in the bulk-logged recovery model - and the index rebuild was running.Do not switch to simple recovery model...that will break your log chain and cause more harm that using the extra space could ever cause.You should also immediately follow that process with a differential backup (or full if you have the time).Also be aware that this doesn't save space in the transaction log backup. The log backup will have all transactions included and will be the same size regardless of the recovery model.And finally, if you want to use any of the AlwaysOn/Availability Group features then switching recovery models is not possible. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-04-07 : 01:49:09
|
| Ranvir_2k - are you running out of disk space?or are your log files not expanding quickly enough?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
SQL Server Index Rebuild/Reorg Recovery Model