Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

4 Answers
4

Simple or full, the same data will be written to the transaction log. The difference is that in simple recovery the log will be truncated between operations, in full you need to backup the log to free log space to be re-used.

One or two tables might be responsible for the majority of the fragmentation. If that's the case, you might find your log growing to a similar size regardless of the recovery model.

You've determined that this database requires full recovery. Can you take the risk of switching to simple, a problem occurring and being unable to point-in-time restore?

You're maintenance process generates 25GB of log. On a state of the art, paying-through-the-nose SAN array that's not an expensive chunk of disk you need.

One approach I've taken in the past is to incorporate log backups in to the reindex/rebuild scripts. Record the log size and free percentage before processing each table, check free percentage and size afterwards. If less than x% of space is free or if log growth has occurred, backup the log.

I'm marking this as the answer, because I believe we're going to look at custom scripting to do the index rebuild, based on the current fragmentation percentage of each index. And we'll also implement a transaction log dump in the script to clear up space.
–
jonvanNov 14 '11 at 15:45

Yeah, I think we could sacrifice that little bit of time during the index rebuild. Our rebuild task only lasts about 15 minutes, and there shouldn't be much new data coming into the system at 3am anyway. So I'd basically just be looking at a second full backup to restart the log chain.
–
jonvanNov 11 '11 at 23:22

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.

If we ran a full backup prior to the rebuild job, then switched to simple mode for the rebuild. Once the rebuild job finishes if I switch back to simple and run another full backup I should be covered, correct? Aside from the 15 minutes or so that the rebuild task is running? I'm likely going to go with the route of building a script to do the index rebuild, and incorporate log backups throughout the script. However, I think in the long run, that will still require just as much additional HDD space. But I think that will still be more beneficial than having a 25gb log file to truncate.
–
jonvanNov 12 '11 at 14:56