Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 Index Maintenance Plan failing Expand / Collapse
Author
Message
Posted Monday, December 02, 2013 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:55 AM
Points: 112, Visits: 362
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?

Post #1518880
Posted Monday, December 02, 2013 10:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 14,797, Visits: 27,271
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1518952
Posted Monday, December 02, 2013 10:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:55 AM
Points: 112, Visits: 362
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
Post #1518960
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse