Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sharepoint 2010 SQL Maintenance Plans

Posted on 2011-03-09
12
Medium Priority
?
1,898 Views
Last Modified: 2012-05-11
Looking for verification here please :-

Propose setting up SQL Maintenance plan on our two servers as follows :-

1. Weekly run DBCC check
2. Monthly rebuilt indexes

The backups on all content DBs are nightly, and all System DBs weekly. I have a couple of questions :-

1. When you run the DBCC check through a maintenance plan, if there are issues where are these output to ? Should you therefore run this, then check the output, then run Repair (if necessary) or should you run the DBCC check and LINK it to DBCC repair to run only after successful run of the first part - or should you simply run with the repair option ONLY ?
2. If the databases are synchronously mirrored, will any changes need to be made to the proposed plan (I am thinking here that there would be no need to backup anything on the secondary mirrored copy as this will be in sync, but also, are there any extra/different recommended practices when using mirroring ?)

Thanks
0
Comment
Question by:TheGeezer2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35083312
2. I don't believe so, although you may only want to reorganize the index rather than rebuilding.  But it depends on the frag level.


good article: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx
0
 
LVL 6

Assisted Solution

by:sabby447
sabby447 earned 200 total points
ID: 35089860
The plan looks fine, DBCC Check should be followed by repair, Only repair is not a good option

For deframent .. Its good to rebuild the indexes, Here is a microsoft article if you would like to do this via code : http://support.microsoft.com/kb/943345 

this has helped me with perf issues over sharepoint and project server over last couple of years

If you have any payment or financial transaction information in the sharepoint site then it is better to use the hourly backup and i hope you are pushing the backups outside of the server and not keeping it locally as backup is no good if they are on same server in terms of crisis
0
 
LVL 11

Author Comment

by:TheGeezer2010
ID: 35146498
I have been checking out the articles on this - all are in their own way confusing. I ran the DBCC CHECK and it found no errors. I have set this to run weekly - all databases. I have created a DBCC REPAIR job, to be run on demand. I will run this ONLY if there is an issue with DBCC CHECK.
I have been looking at the Sharepoint 2010 jobs and can see that both re-indexing and updating stats is already taken care of on a weekly basis !!

OK so this being the case, do I need to still worry about re-indexing and updating stats for system databases ? Has anyone had experience of the Sharepoint jobs running, and do you need to do anything extra in SQL ?

Thanks again for responses.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 38

Expert Comment

by:Justin Smith
ID: 35147453
Updating stats - not really.  SharePoint should be doing this for you.

I typically have clients re-org the index monthly.
0
 
LVL 11

Author Comment

by:TheGeezer2010
ID: 35147537
Hi Ach1lles

The jobs within SP do the following :-

 Application pools recycle when memory limits are exceeded. Weekly Yes No
 Databases used by SharePoint have fragmented indices. Daily Yes Yes
 Databases exist on servers running SharePoint Foundation. Weekly Yes No
 The paging file size should exceed the amount of physical RAM in the system. Weekly Yes No
 Databases used by SharePoint have outdated index statistics. Daily Yes Yes
 The timer service failed to recycle. Weekly Yes No
 Search - One or more property databases have fragmented indices. Weekly Yes Yes
 Search - One or more crawl databases may have fragmented indices. On Demand Yes Yes

So from this, looks like the indexes are checked and (if necessary) reorganized daily - doesn't actually say if this involves rebuilding indexes but given the obtrusive nature of that, I would think it unlikely.
I am still minded to go for rebuilding indexes on all datbases (system, content, search etc) on a monthly basis. Any final thoughts on the wisdom of this ?
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35147577
Yes!  Re-org them montly.

What you referenced above are only "checks".  They aren't jobs.
0
 
LVL 11

Author Comment

by:TheGeezer2010
ID: 35148097
Hi Ach1lles

Firstly I think you are incorrect about the above referenced jobs since the filter at the end denotes whether the job is set to "Repair automatically" so I believe the jobs WILL do either re-indexing/re-organization of indexes and/or updating stats.

Secondly, your last comment, did you mean Re-Org monthly or Rebuild indexes monthly. I ask because my final comment was to state that I am minded to do rebuilding of indexes monthly ?
0
 
LVL 11

Author Comment

by:TheGeezer2010
ID: 35149055
I think as well, after reading several articles, I am going to drop the DBCC REPAIR for two reasons :-

1. REPAIR is only recommended as a last resort
2. By Rebuilding the Indexes, this is a far more effective way of repairing most issues.

Comments ?
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 35150358
Correct, re-org.  I've never had so many replies to a 250 pt question LOL
0
 
LVL 11

Author Comment

by:TheGeezer2010
ID: 35179116
OK I have one last question before I allocate points and close. If SP 2010 loses contact with its SQL back end, it causes a big problem with SP itself (not surprisingly). The correct method is to take SP down gracefully, then IIS THEN bring SQL databases offline, then the reverse when bringing back up. My question therfore is this (and is still on the original theme) :-

If I am to run a DB REINDEX montly, should I firstly run a batch file to bring SP down gracefully (it will not be in use at the time I run the re-index but I am concerned that if SP is not brought down gracefully, it will not be recoverable once maintenance completes). Am I right to be worried about this ? What are other people doing ?

Thanks
0
 
LVL 38

Accepted Solution

by:
Justin Smith earned 800 total points
ID: 35179506
If it looses contact, the sites will go down and timer jobs may fail.  However, it will be restored when it gains contact again.  

You don't need to bring down your SP servers beforehand.  I've managed nearly 100 farms and have never brought SP down priror to reorging the index.  
0
 
LVL 11

Author Closing Comment

by:TheGeezer2010
ID: 35180090
Thanks to those who assisted. Final plan :-
Weekly DBCC CHECK all databases
On Demand DBCC repair
Monthly RE-INDEX all CONTENT databases + MSDB
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question