Creating maintenance plans for Project Server 2007 databases
Updated: September 19, 2008
If you are unfamiliar with SQL Server operational best practices and want to begin using SQL Server quickly and safely, then we recommend adoption of SQL Server maintenance plans. These are specifically designed for smaller application implementations where the benefits of having something quick and easy outweighs the control given by designing custom maintenance schemes.
If your organization has staff trained in supporting SQL Server applications, these staff are most ideally suited to design the operational schedule based on the service levels demanded by the organization.
Many of the database maintenance operations covered in this document 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 2005 or SQL Server 2000, an administrator can schedule such operations as running database consistency checks, reorganizing or rebuilding indexes, and shrinking databases to reclaim unused space. For more information, see the following resources:
Maintenance Plan Wizard (http://go.microsoft.com/fwlink/?LinkId=128478&clcid=0x409) for SQL Server 2005
Database Maintenance Plan Wizard (http://go.microsoft.com/fwlink/?LinkID=110849&clcid=0x409) for SQL Server 2000
If you are planning to use the Maintenance Plan Wizard on SQL Server 2005, make sure that SQL Server 2005 Service Pack 2 is installed. Versions prior to Service Pack 2 do not reapply index options after it rebuilds an index using a maintenance plan. Executing this task on versions of SQL Server 2005 without Service Pack 2 (or a later service pack) will likely cause severe damage your databases.
When creating a database maintenance plan for your Microsoft Office Project Server 2007 deployment, consider the following best practices:
Ensure that you have reliable backups for all databases before you implement maintenance operations and maintenance plans.
Before you implement consistently running maintenance operations or a maintenance plan, test the impact of the operations on your system and the time required to run them.
As much as possible, set any maintenance operations or maintenance plans to run during off hours to minimize the performance effect on users.
If you have an environment with ten or more content databases or more than 250 GB of content, we recommend that you configure separate maintenance plans to provide appropriate specificity and to maximize the maintenance window.
When you are creating a maintenance plan using the Maintenance Plan Wizard, we recommend the following best practices:
A maintenance plan should include either index reorganization or index rebuilding, not both.
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 finish during hours when end-user operations will not be negatively affected.
Always begin with the database integrity check. If the integrity check fails, do not perform the remaining tasks. Instead, repair the suspect database.
To maintain a sustainable and stable SharePoint Products and Technologies deployment, content databases are the only databases that you should shrink. (Deletion is most common in content databases.) Shrinking the configuration database, Central Administration content database, SSP databases, and Search databases is unnecessary and can lead to fragmentation.
When configuring database shrinking, set the database to shrink when it reaches 20 percent more than the maximum size you want your content databases to grow. For example, if you have established a database architecture that allows up to 100 GB for each content database, set this value to 120 GB.
Retain 10 percent of free space after a database shrink operation. Setting this value can help reduce fragmentation when you have scheduled frequent shrinking.
When rebuilding an index, set the free percentage per page to 70 percent. This sets the fill factor for the database.
More complex maintenance plans are best implemented in SQL Server Integration Services. It provides the same maintenance task options as SQL Server maintenance plans with the option to add event handlers, integrate with outside systems such as workflow or Operations Management systems, specify complex conditional execution, and utilize variables to store state.