SQL Rebuild Index, Recovery Model, and db log filesize?

I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am.

Every Saturday at 3am, we have a job setup to rebuild the indexes on all tables.

That being said, doing the index rebuild causes our transaction log to grow greatly. I'm toying with different ideas to alleviate the extra drive space needed (approximately 25gb post re-index).

I was considering setting the database recovery model to simple prior to the rebuild task, to prevent all of the index rebuilding from being logged, and then setting it back to full once the rebuild is complete.

Is anyone else using this method? Or can anyone provide insight/advice as to why this may be a bad idea? Or any tips about how to handle huge log files while performing db maintenance tasks?

Switching to SIMPLE means you break the log chain. When you 'revert' to FULL you need to start a new log chain, meaning you have to take a full backup and start taking again new log backups. The switch to simple, no matter how short, in effect creates a new 'epoch' in your backup chain, as any backup from before the switch to simple can no longer be applied to the database after the switch, nor the other way around.

So at this moment you have to stop and think: what is the business requirement that made you have FULL recovery model to start with? Whatever reason it is, is unlikely that it can be 'suspended' every Saturday at 3am, and is just unlikely that it will tolerate your 'epoch' situation in which you can restore back in time from Friday to Thursday, but you cannot from Saturday to Friday because Saturday is a new 'epoch'. In other words, if you have a business requirement for FULL recovery model, then you better not break it.

But if you do not have a business requirement for FULL recovery model, then you have room to play. And I do not mean to switch to SIMPLE, I mean use the 'other' recovery model: BULK_LOGGED. The reason your reindex operations generate copious log is that they occur under FULL recovery model. Under BULK_LOGGED the index rebuild (both offline and online) are going to use minimally logged operations, see Operations That Can Be Minimally Logged:


If the database is set to the simple or bulk-logged recovery model,
some index DDL operations are minimally logged whether the operation
is executed offline or online. The minimally logged index operations
are as follows:

• CREATE INDEX operations (including indexed views).

• ALTER INDEX REBUILD or DBCC DBREINDEX operations.

• DROP INDEX new heap rebuild (if applicable).

So, if possible, switch the database recovery model to BULK_LOGGED and leave it as such.

StackExchange Database Administrators Q#7786, answer score: 7