Take the 2-minute tour ×
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.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
"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

 
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.