March 16, 2009 at 3:43 am
Hello,
I have an SQL server that we use for both Sharepoint MOSS and Sage 200.
At the moment I run weekly maintenance plans (the wizard created ones in management studio) that do Database checks, re index, reorganise and shrink.
Sage is only used by 3 or 4 people, but the weekly plan takes about 10hrs to run on about 10 databases (about 3gb total) whereas it takes about 20 mins to run on the Sharepoint DBs (about 10gb)
The problem is we are about to start using a DR company that uses Double-Take, and they noticed that obviously when the maintenance runs for Sage, it makes about 6gb of changes that needs to be replicated over our WAN now to this company.
Since there are only 3 users, is it necessary to run these plans? I don't actually know what they do I just thought it would be a good idea to set them up last year, no one has ever complained about slowness etc. Could I change Sage to run once a month?
Thank you
March 16, 2009 at 7:28 am
Regular maintenance is definitely a good idea, but many times the default configuration of maintenance plans is more maintenance than is actually necessary. Kimberly Tripp just wrote an excellent blog post about maintenance that you might want to read.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2009 at 9:21 am
callum.bundy (3/16/2009)
At the moment I run weekly maintenance plans (the wizard created ones in management studio) that do Database checks, re index, reorganise and shrink.
Bad combination of tasks. The reindex does everything that the reorganise does, and more, then the shrink will go and scramble your nicely defragmented indexes.
Suggestion - drop the reorganise, drop the shrink, go read Kimberly's blog post (linked in above post)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2009 at 10:41 am
Read the blog, feel a bit silly now.
Changed all my plans, dropped re-index and shrink so I will check how many GB of change there is on the weekend
Thanks guys
PS if its still making huge changes, can I drop the reorganise?
March 16, 2009 at 1:16 pm
callum.bundy (3/16/2009)
Read the blog, feel a bit silly now.Changed all my plans, dropped re-index and shrink so I will check how many GB of change there is on the weekend
Thanks guys
PS if its still making huge changes, can I drop the reorganise?
Before dropping it you need to analyze the percentage of fragmentation of your leaf level pages and then decide if it is small amount around <5% I would not bother defragging them.
Rule of thumb: 30% Rebuild them.
March 16, 2009 at 1:34 pm
callum.bundy (3/16/2009)
PS if its still making huge changes, can I drop the reorganise?
Not a good idea. You should either rebuild or reorganise indexes relatively regularly. If you don't want to be burdened with complex main plans, rebuild once a week.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2009 at 6:26 am
Thanks, you've been a great help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply