Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1906
  • Last Modified:

Sharepoint 2010 SQL Maintenance Plans

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
TheGeezer2010
Asked:
TheGeezer2010
  • 6
  • 5
2 Solutions
 
Justin SmithSr. System EngineerCommented:
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
 
sabby447Commented:
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
 
TheGeezer2010Author Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Justin SmithSr. System EngineerCommented:
Updating stats - not really.  SharePoint should be doing this for you.

I typically have clients re-org the index monthly.
0
 
TheGeezer2010Author Commented:
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
 
Justin SmithSr. System EngineerCommented:
Yes!  Re-org them montly.

What you referenced above are only "checks".  They aren't jobs.
0
 
TheGeezer2010Author Commented:
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
 
TheGeezer2010Author Commented:
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
 
Justin SmithSr. System EngineerCommented:
Correct, re-org.  I've never had so many replies to a 250 pt question LOL
0
 
TheGeezer2010Author Commented:
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
 
Justin SmithSr. System EngineerCommented:
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
 
TheGeezer2010Author Commented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now