SharePoint Consulting Blog

Microsoft SharePoint 2010 databases are very large in size and it's essential to maintain it for smooth and faster database operations. SharePoint recommends following task to perform for SharePoint Database.

Check database integrity.
Defragment indexes by reorganizing them or rebuilding them.
Set the fill factor for a server.

Database maintenance task covers all required process to maintain SharePoint database. Database maintenance task can be performed by running database maintenance wizard or by running Transact-SQL commands. Transact-SQL commands are very complex and need deep knowledge of SQL commands and configuration. But SQL server management studio 2008 and 2005 covers database management wizard with very user friendly user interface. Here we will cover SharePoint database maintenance with maintenance plan wizard.
Do following process to run maintenance plan wizard with SQL Server Management studio 2008.

Open SQL Server management studio and do login.
Select Management and right click maintenance plans and Choose Maintenance Plan wizard.
Click next until select plan property page.

Give name and description as desired.
Select weather configures one or more maintenance plans.
- To configure a single maintenance plan, select Single schedule for the entire plan or no schedule.
- To configure multiple maintenance plans with specific tasks, select Separate schedules for each task.
For the database more than 10 content databases or having larger size of database, it is recommended to go for separate maintenance plans.
Click on change button to create a schedule or timer job to run maintenance plan.

It will open Job Schedule Properties dialog box.
- Set schedule as per requirement.
- After scheduling click OK and Next.
Next page is to select maintenance tasks page. This page will show the list of all maintenance tasks required for SharePoint Maintenance.

Note:
- Need to take care in selecting index reorganization or index rebuilding. A maintenance plan should include any one from index reorganization and index rebuilding, Not Both.
- A maintenance plan should never include shrinking a database.
- Maintenance cleanup task will remove files left after scheduled Maintenance.
Select the tasks for the plan and click next.

Next page will be Select Maintenance Task Order Page.
- You can change the order of the tasks in the plan on this page. Then click next.
Next page will be Database Check Integrity Task Page.
- Select databases to reorganizing index and check compact large object check box. Then click ok.
Define Rebuild Index Task page
- Select databases and configure as below. And click next.
Define Maintenance Cleanup Task page
- Select options as required and click next.
Select Report Options
- Give path to save log file.
Then click finish to start the maintenance plan process.
To shrink database do following process.
- Start SQL server management studio 2008.
- In Databases, select the database you want to shrink.
- Right click the database, select tasks, Shrink, files.
- Select the file type and file name.
- Select reorganize files before releasing unused space.
- Then click OK