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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL Server Index Rebuild/Reorg Recovery Model

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-26 : 22:09:07
We increase the log backup frequence during Index Housekeeping (to every 2 minutes)
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -