2

System:

I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total).

Issue:

During the Index Rebuild (~20 minutes), we fail to insert data into the DB

Question:

How can I reduce the downtime during the Maintenance Plan? I was thinking about ensuring the Transaction Log size is small (Truncating it 4 times a day), but I don't think that will help.

I'm also uncertain if Rebuilding the Indexes more often will help. (Since I don't know if this is a "bulk" operation that will benefit from recently Re-Indexed Tables).

Note: Upgrading to SQL Server 2005 Enterprise for the Live Re-Indexing feature is not an option.

6

Why are you rebuilding the indexes? Do you have any evidence that the rebuild is required/needed/beneficial? Have you considered when to use Reorganize vs. Rebuild?

Consider using one of the many index maintenance scripts like the ones from Ola Hallengren or Michelle Ufford. These scrips have intelligence in them to do appropriate maintenance action according to the index fragmentation and will reduce the offline duration by rebuilding only when truly required (hopefully never).

IF a rebuild is needed then there is nothing you can do w/o going to an Enterprise Edition and using online index rebuild.

Oh, one more thing: do not shrink the log. You are only causing it to take more time when it grows back.

4
  • "do not truncate the log. You are only causing it to take more time when it grows back." Truncating the log will not cause the actual file to shrink, therefore it won't have to autogrow back to size. – Thomas Stringer Mar 28 '12 at 19:31
  • @Shark: from the general misuse of terms and concepts in the OP I became convinced it uses 'truncate' but it really means 'shrink'. – Remus Rusanu Mar 28 '12 at 19:33
  • Ah, good point. :) Sorry for the not-needed correction, Remus. – Thomas Stringer Mar 28 '12 at 19:34
  • @RemusRasanu: My mistake. I meant to say "Backup Transaction Log" (which I believe implies a Truncate + Shrink, but I may be mistaken). – Onion-Knight Mar 29 '12 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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