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.
Best Answer
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.