The database maintenance tasks supported for SharePoint 2010.
The recommended maintenance tasks for SharePoint 2010 databases include:
* Checking database integrity.
* Defragmenting indexes by either reorganizing them or rebuilding them.
* Setting the fill factor for a server.
Database maintenance and not planning for capacity or performance. För information about capacity or capacity planning, see Storage and SQL Server capacity planning and configuration
(SharePoint Server 2010) http://go.microsoft.com/fwlink/?LinkId=217482.
SharePoint required manual intervention to perform index defragmentation and statistics maintenance, SharePoint 2010 automates this process for its databases. This is accomplished by several SharePoint Health Analyzer rules. These rules evaluate the health of database indexes and statistics daily, and will automatically address these items for these databases:
* Configuration Databases
* Content Databases
* User Profile Service Application Profile Databases
* User Profile Service Application Social Databases
* Web Analytics Service Application Reporting Databases
* Web Analytics Service Application Staging Databases
* Word Automation Services Databases
Database maintenance tasks can be also performed by either executing Transact-SQL commands, or running the Database Maintenance Wizard. We will initially present the Transact-SQL commands that you can use, and then explain how to create database maintenance plans by using the Microsoft SQL Server® Database Maintenance Wizard.
Check for consistency errors by using DBCC CHECKDB
Start your routine maintenance operations with consistency checks to ensure that your data and indexes are not corrupted. You can use the DBCC
CHECKDB statement to perform an internal consistency check of the data and index pages.
The most of database consistency problems are caused by I/O subsystem errors.
Database consistency checks should be performed at least once per week on your SharePoint 2010 databases, and whenever events such as database server or I/O subsystem failures occur.
DBCC CHECKDB:
DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
1. Runs the equivalent of DBCC CHECKALLOC (http://go.microsoft.com/fwlink/?LinkId=110815&clcid=0x409) to verify the allocation structures in the database.
2. Runs the equivalent of DBCC CHECKTABLE (http://go.microsoft.com/fwlink/?LinkId=162093) on every table and view in the database to verify their logical and physical integrity.
3. Runs the equivalent of DBCC CHECKCATALOG (http://go.microsoft.com/fwlink/?LinkId=110834&clcid=0x409) on the database to verify its metadata consistency.
Running DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS is not supported. However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.
DBCC CHECKDB and performance:
DBCC CHECKDB causes blocking” (http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(230)-DBCC-CHECKDB-causes-blocking.aspx). by Paul S. Randal.
You may find that the resource overhead of running DBCC CHECKDB is too high for your production system. In that case, do not attempt to run consistency checks one table at a time as this will be more problematic overall. The best ways to reduce the integrity-checking overhead on the production system
sqlboob.wordpress.com 