Solved

Sharepoint 2010 SQL Maintenance Plans

Posted on 2011-03-09
12
1,808 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
  • 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 50 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 200 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now