December 2, 2013 at 6:50 am
I have two SQL Server 2008 instances that are having issues with SQL Server Maintenance plans that "Rebuild" indexes on a weekly basis. The one instance has large databases over 100GB in size. The plan fails when it the lock time period is exceeded. I thought I would try the "online" rebuild.... but that is only available on "Enterprise" editions. I went through and was able to manually "Rebuild" all fragmented indexes. Is there a better way to do this?
On the other instance, when the weekly Index Rebuild Maintenance Plan runs... it cause the "LDF" file to get so large that it blows off the disc. Should I run "Backup Log" jobs at a close interval during the Rebuild to prevent this from occurring?
December 2, 2013 at 10:41 am
You're basically running into limitations of the generic nature of Maintenance Plans. If they work for you, great. If they don't, the only real recourse you have is to start scripting your own processes. But, you can start with other scripts. The two most frequently used are Ola Hollengren's scripts and Michele Ufford's scripts (just look up the names, you'll find the links). They'll get you going better than using a Maintenance Plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2013 at 10:59 am
Thank you for the reply/information. I actually have been trying to get management to let me install the "Ola" script for a long time now.... but..... did I mention it is Management.... as soon as they say it is their idea.... I will be allowed to apply the script.... but your comment will be used to fuel the fires!
Charlie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply