Many of the database maintenance operations covered in this white paper can be programmatically applied through the implementation of SQL Server maintenance plans. Maintenance plans can both automate and schedule essential tasks to protect your data. By using maintenance plans in SQL Server 2008 or SQL Server 2005, an administrator can schedule such operations as running database consistency checks, and reorganizing or rebuilding indexes. For more information, see the following resources:

• Maintenance Plan Wizard (http://go.microsoft.com/fwlink/?LinkId=110855&clcid=0x409) for SQL Server 2008
• Database Maintenance Plan Wizard (http://go.microsoft.com/fwlink/?LinkId=217494) for SQL Server 2005

To configure a SQL Server 2008 database maintenance plan

• On the taskbar, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
• In Object Explorer, connect to an instance of the SQL Server 2008 Database Engine and then expand that instance.
• Click Management, right-click Maintenance Plans, and then click Maintenance Plan Wizard.
• Click Next until you reach the Select Plan Properties page.

• In the Name and Description fields, type a name and description.
• Decide whether to configure 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.

If you have an environment with 10 or more content databases or more than 200 GB of content, we recommend that you configure separate maintenance plans to provide appropriate specificity and to maximize the maintenance window.

If you configure multiple maintenance plans for a database, specify a name or description that enables you to differentiate the plans and their purposes, including their schedules.

• Click Change to set a schedule for one or more plans.

The Job Schedule Properties dialog box appears.

• Complete the schedule, click OK, and then click Next.
• On the Select Maintenance Tasks page, select the maintenance tasks to include in the plan, and then click Next.

 
Notes:

• A maintenance plan should include either index reorganization or index rebuilding; not both.
• A maintenance plan should never include shrinking a database.
• To determine the duration of each task, test each task individually before combining tasks into a single plan. You may need to define several maintenance plans on separate schedules to allow tasks to complete during hours when end-user operations will not be negatively affected.
• The Maintenance Cleanup task removes files left over from executing a maintenance plan.

• On the Select Maintenance Task Order page, change the order of the maintenance plan tasks if needed. Select a task, and then click Move up or Move down. When tasks are correctly ordered, click Next.

Note: If your databases are very large, you may want to create a separate maintenance plan for checking database integrity less frequently than index maintenance.

Next, the wizard guides you through setting the details for each task.

• On the Define Database Check Integrity Task page, select the databases to check for integrity, and then click Next.

Note: You can safely check all SharePoint 2010 databases for integrity.

• On the Define Reorganize Index Task page, in the Databases list, specify the databases to reorganize the indexes for, select the Compact large objects check box, and then click Next.

• On the Define Rebuild Index Task page, if you have chosen to rebuild indexes instead of reorganize those, in the Databases list; specify the databases to reorganize the indexes for.
• Select Change free space per page percentage, type 80, and then click next.

Change free space per percentage sets the fill factor for the database.

• On the Define Maintenance Cleanup Task page, set the values that meet your needs, and then click Next.

• On the Select Report Options page, select Write a report to a text file, select a location for the files, and then click Next until the wizard is completed.

Source: TechNet.com