Tell me more ×
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.
up vote 5 down vote favorite
3

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?

share|improve this question

migrated from stackoverflow.com Nov 12 '11 at 0:11

4 Answers

up vote 3 down vote accepted

There are a number of considerations here:

  • 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.

share|improve this answer
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. – jonvan Nov 14 '11 at 15:45
up vote 4 down vote

Note that while you're in "simple" mode you lose the ability to restore to a point in time from transaction log backups.

When you switch back to "full" mode you must perform a full backup to reestablish the log chain. You won't be able to resume transaction log backups until you've done a full backup.

We're struggling with the same thing here, and so far the plan is to do log backups more frequently and keep them on external storage (i.e. not on the same server).

share|improve this answer
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. – jonvan Nov 11 '11 at 23:22
up vote 4 down vote

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.

share|improve this answer
4  
SQL2008+ all online index rebuild operations are fully logged, so BULK_LOGGED will not help. See You may notice an increased transaction log sizes in SQL Server 2008 and later versions when you perform Index Maintenance. – Mark Storey-Smith Nov 12 '11 at 1:59
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. – jonvan Nov 12 '11 at 14:56
up vote 1 down vote

Before considering a change to simple mode, I would look at:

  1. Change the index rebuild job to only rebuild based on fragmentation level.
  2. Partitioning, rebuild only the active partitions.
  3. Spread the workload across multiple nights.
  4. Check your clustered indexes to make sure they fit your record additions.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.