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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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, 2012 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, 2012 at 19:33
  • Ah, good point. :) Sorry for the not-needed correction, Remus.
    – Thomas Stringer
    Mar 28, 2012 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, 2012 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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