Project Server Help, FAQs, Blogs, and HowTos
This is The Header Then

Maintenance Plans for Project Server 2007 DBs

posted January 3rd, 2008 by Stephen Sanderlin
1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.33 out of 5)
Loading ... Loading ...

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):

Step One: Connect SQL Management Studio

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:

Step Two: Open Maintenance Plan 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 >:

Step Three: 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:

Step Four: Name your Plan

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:

Step Five: Schedule the Plan

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 >:

Step Six: 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 >:

Step Seven: Choose Maintenance Tasks

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 >:

Step Eight: Select Maintenance Task Order

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:

Step Nine: Define Database Check Integrity Task - Part OneStep Ten: Define Database Check Integrity Task - Part Two

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.

Step Eleven: Shrink Database Task - Part OneStep Twelve: Shrink Database Task - Part Two

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:

Step Thirteen: Reorganize Index Task - Part OneStep Fourteen: Reorganize Index Task - Part Two

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:

Step Fifteen: Rebuild Index Task - Part OneStep Sixteen: Rebuild Index Task - Part Two

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:

Step Seventeen: Update Statistics Task - Part OneStep Eighteen: Update Statistics Task - Part Two

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.

Step Ninteen: History Cleanup TaskStep Twenty: Maintenance Cleanup Task

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:

Step Twenty-One: Select Report Options

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

Step Twenty-Two: Complete the Wizard

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

Stephen Sanderlin is Vice President of Technology for MSProjectExperts and a Microsoft Project MVP. His earlier writings on Project Management and Microsoft Project can be read at EPMFAQ.
He is actively posting new content at ProjectServerHelp.

Popularity: 41%

Discuss this post on the EPMFAQ Blog Posts Forum.

Related Posts

« Previous Entry Next Entry »

Please discuss this post on the EPMFAQ Blog Posts Forum.

One Response to “Maintenance Plans for Project Server 2007 DBs”

  1. […] http://www.epmfaq.com/ssanderlin/project-server-2007/maintenance-plans-for-project-server-2007-dbs (Maintenance Plans for Project Server 2007 DBs) […]

The opinions expressed on this site by the authors and those providing comments are theirs alone, and do not reflect the opinions of the respective employers of the authors or any employee thereof. Our respective employers are not responsible for the accuracy of any of the information supplied by on this site.

Terms of Use | Privacy Policy | Publishers

All content on this website is covered by the
Creative Commons Attribution-Share Alike 3.0 United States License.
Theme ©2007 The Heckerped WordPress Theme created by JTk of Doc5 under the Creative Commons Attribution License.