As I’ve previously discussed, at my present client we recently were working very closely with Premier Support to resolve some issues with the cube build. Throughout the course of these discussions, I was told that I really should be running DB Maintenance Plans on the PS07 DBs because with the switch to GUIDs in Project Server 2007, the indices in the various databases can become stale very quickly.

My response to Premier was that since Microsoft has provided no guidance on doing this, and since Microsoft has made such a big deal about not touching any of the databases except Reporting (or Published in very limited circumstances) implementers (including myself) are concerned about doing ANYTHING with regards to Maintenance Plans without guidance from Microsoft.

Premier responded that Chris Fiessinger wrote a blog post about this recently. The agent I was working with gave me advice on how to set up the jobs in the maintenance plan, and promised to press the Product Group to put out some official guidance in the near future.

So, here are directions on setting up Maintenance Plans for the Project Server 2007 DBs.

You should perform these steps only against the Project Server 2007 databases. For advice on performing these tasks against SharePoint databases, please consult KB943345 and KB932744.

Please use caution when implementing these instructions in a production environment.

If you choose to do so, you are doing so at entirely your own risk.

I very strongly recommend that you test in your development and staging environments before applying to production, and as usual, I bear no responsibility for any issues, data loss, financial costs, or downtime you may incur from following this HowTo.

If you don’t know what a Maintenance Plan is or why running one is important, you probably shouldn’t do this yourself. If this is the case, it would be better for you to pass this article along to your DBA.

First, we want to open SQL Management Studio and connect to the database instance where the Project Databases are stored (note that if you have segmented the Reporting database from the other databases, you will have to do this entire procedure twice):

Next, we want to expand the Management section in Object Explorer. Right-click on Maintenance Plans, and left-click Maintenance Plan Wizard to start up the wizard:

If you haven’t run the Maintenance Plan Wizard before (or never checked the Do not show this starting page again. option), you will see a brief introductory screen to the wizard. Just click Next >:

Now, we come to the Select Plan Properties step (recall that if you have previously checked the Do not show this starting page again. option on the starting page of the Wizard, you will come directly here). Modify the Name field to whatever you wish (I called mine PS07DBs_Maint_Plan). I recommend that you select the Single Schedule for the entire plan or no schedule option, as setting up seperate schedules is unneccessary (at least in my experience). Then, click Change to set up the schedule:

You can set the options in this step to whatever you would like. I recommend that you speak with your DBA and SysAdmin departments to determine the best time for execution in relation to database and server backups. I would also schedule the plan either way before or way after your cube build. Once you’re done, click OK:

You’ll be returned to the Select Plan Properties step, where the Schedule field will contain the details of the schedule you just configured. Once again, just click Next >:

Finally we come to the Select Maintenance Tasks step, where it’s time to choose the tasks we want the plan to execute. I recommend you choose the following tasks:

• Check Database Integrity
• Shrink Database
• Reorganize Index
• Rebuild Index
• Update Statistics
• Clean Up History
• Maintenance Cleanup Task

You don’t have to select the Update Statistics task, since the SharePoint Timer Service does this periodically for you, but I selected it anyway.

If you have not applied SQL Server 2005 Service Pack 2 or above, do not under any circumstances run the Rebuild Index task. There is a bug in versions of SQL Server 2005 prior to SP2 that causes the product to 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 above is not safe and will very likely severely damage your databases!

When you are finished selecting your options, click Next >:

On the Select Maintenance Task Order step, you can reorder the tasks, but I left them as the default. In fact, I recommend leaving pretty much all of the options we will come across as their defaults unless either you or your DBA have a good reason to alter them. In this case, make whatever changes you would like, and click Next >:

The final steps involve going through each of the tasks and setting up the options. First up is the Database Integrity Check Task. I recommend you select the Specific Databases option, choosing the four Project Server 2007 databases. Make sure that after selecting the databases, you check off Include indexes so that they get checked as well:

The Shrink Database Task is next. Here we want to do the same thing as before - choose the four Project Server 2007 databases, and set the task options. In this case, you can set these options pretty much however you want - the point of this task is simply to reduce the size of the databases.

Next is the Reorganize Index Task. Again, select the four Project Server 2007 databases, then set the task options. As with the Database Integrity Check, there’s only one option: Compact large objects. I would leave this option selected:

Now, we get to the Rebuild Index Task — the central focus of this endeavor. As before, select the four Project Server 2007 databases, then set the options. I configured this task to simply Reorganize pages with the default amount of free space, rather than entering a percentage. I also left the advanced options both off — the server I was deployed this plan to had more than enough memory to hold the sort results outside of the tempdb, and I was running the rebuild off-hours, thereby making it unnecessary to keep the index online. You should change these options depending on your environment and requirements:

Almost done! The Update Statistics Task is next. You know the deal by now — select the four Project Server 2007 databases and set the options. I would just leave the options set to All existing statistics and Full Scan, but you can alter these options if you wish:

I’m going to group the next two together, since they’re the cleanup tasks. You should definitely get your DBA’s input on these settings, since they likely have retention policies in place (either for just purposes of retention, or SOX compliance). I’m including the screens for your reference, but please make sure you are well versed with your organization’s standards and policies before cleaning up any backups or history.

Last task! We want to select the Report Options to capture the output of the Maintenance Plan. You can either save to a file, or email the report:

On the final screen, just review all the options you selected to make sure they are all correct:

That’s pretty much it! I’ll post a followup as soon as something comes out from the product group. Until then, happy maintaining!

Popularity: 57%