TheGeezer2010
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Updating stats - not really. SharePoint should be doing this for you.
I typically have clients re-org the index monthly.
I typically have clients re-org the index monthly.
ASKER
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 ?
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 ?
Yes! Re-org them montly.
What you referenced above are only "checks". They aren't jobs.
What you referenced above are only "checks". They aren't jobs.
ASKER
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-organizatio
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 ?
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-organizatio
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 ?
ASKER
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 ?
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 ?
Correct, re-org. I've never had so many replies to a 250 pt question LOL
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to those who assisted. Final plan :-
Weekly DBCC CHECK all databases
On Demand DBCC repair
Monthly RE-INDEX all CONTENT databases + MSDB
Weekly DBCC CHECK all databases
On Demand DBCC repair
Monthly RE-INDEX all CONTENT databases + MSDB
good article: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx