Creating maintenance plans for Project Server 2010 databases
Applies to: Project Server 2010
Topic Last Modified: 2011-06-20
If you are unfamiliar with Microsoft SQL Server operational best practices and want to begin to use SQL Server quickly and safely, then we recommend adoption of SQL Server maintenance plans. These are designed specifically 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 necessary tasks to protect your data. By using maintenance plans in Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2, 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 (https://go.microsoft.com/fwlink/p/?LinkId=221726) for SQL Server 2008
Maintenance Plan Wizard (https://go.microsoft.com/fwlink/p/?LinkID=221722) for SQL Server 2008 R2
When creating a database maintenance plan for your Microsoft Project Server 2010 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 effect of the operations on the system and the time that is 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 that has 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 have to define several maintenance plans on separate schedules to enable tasks to finish during hours when end-user operations will not be adversely 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 2010 Products 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 that 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 use variables to store state.