Project Server 2007 Archive DB too large?

Is your PWA Archive database too large? You may have a problem if your archive DB is significantly larger than

(# of retention copies set to be maintained in your PWA’s Server Settings) x (draft DB size)

There are several things to look at:

1. How did you detect the problem? Did you run out of space on a crucial drive, leading to squeals of pain from your users as everything ground to a halt? Did you just happen to notice it while performing some other option? Did you notice your backup times started getting too long?  If so, make a note now: discuss deploying and configuring a good monitoring solution, such as System Center Operations Manager (SCOM) to make sure that next time, you get warnings before they grow into problems. Go ahead, send yourself an e-mail and mark it with a “follow-up this week” flag.

2. As with any DB growing unexpectedly, my first step would be to look at where the growth is occurring. Open up SQL Server Management Studio and connect to your database server. Right click on your archive DB and select “Properties”. Skip to the section below depending on where the growth appears to be

If your transaction log file(s) appear too large

Check your DB recovery type – if it’s set to Full (most production scenarios), go check your backup plan – is it running successfully? Does it include your Archive DB? Without backups executing, your transaction log tail will never rewind, and eventually it’ll start growing, and growing, and growing. Take a look at your processes and fix whatever’s wrong. Then, get yourself out of the immediate hole by executing a full backup (or waiting until your process next kicks one off). This will reset your transaction log tail to the beginning (or near the beginning if it’s being used) of the transaction log. Next, release the space back to the operating system with the following queries:

USE [YOUR_PWA_NAME} GO SELECT * FROM SYSFILES

Note the names of your logfiles, then execute the following query for each:

DBCC SHRINKFILE ('YOUR_LOG_FILE_NAME’,{DESIRED_LOG_FILE_SIZE}, TRUNCATEONLY)

where {DESIRED_LOG_FILE_SIZE} is the number of 8KB page files. To go for broke, I usually set it to 30, but for your production system, you should follow DB best practices and set your transaction log size to be slightly larger than you expect it to max out at – you don’t want to suffer from delays due to transaction log growth during production hours.

If your data file(s) appears too large

First, do a manual check to see if the number of projects available for restoration in your PWA is larger than the amount that is configured. For example, if your PWA is set to retain the past 3 copies of projects in the archives, but you notice that there are 5 copies of a given project in the archive available to restore. Wait until a maintenance window, then enter your PWA’s Server Settings and select “Delete Enterprise Objects”. Make absolutely certain that you check the radio button “only Archive projects” – skipping this step is a recipe for a “please turn in your badge on your way out” situation.  Go through and select all excess projects (pay attention to paging – if you have a lot of projects or a large retention threshold, there may be multiple pages to browse through). You’ll probably want to delete the oldest projects, leaving the most recent, but that’s a business decision.  Be warned – each project you delete from the archive will be processed separately in the queue – if you’re deleting a bunch of projects, you can clog the queue for quite some time. On a fairly beefy environment, it took about 90 minutes to delete 150 projects, and about 4 hours to perform the same task on a virtualized development server.

If you haven’t yet applied the August 2008 Cumulative Update for Project Server 2007 (KB 950961), or if you had retention on before you applied this hotfix, you should check your DB for orphaned records. Brian Smith posted the definitive article at his blog, and I won’t repeat it here other than to add my findings and some follow-on steps. First, before executing the scripts he describes (or any other sql statements directly against your production Project Server DBs), I highly recommend opening a PSS case. They’re well aware of this issue, and will likely let you loose with a warning to test in a separate environment before going against production with little fuss. However, no matter how comfortable you are with Project Server or SQL Server, you’re still executing against an undocumented database, and you really don’t want to find out 6 months down the line during a critsit that your PSS case owner balks because you admitted to performing an unsupported DB action. Also, there may be more tables than are listed at Brian’s blog (as of this posting) that contain orphans. I found an extra 1GB of orphaned records in MSP_Links. I’d recommend executing (with PSS’ support of course) the query

EXEC sp_msforeachtable 'sp_spaceused "?"'

If you notice any particular tables that are large, and weren’t on the list at Brian’s post, talk to PSS about modifying the scripts to check them for orphans.

Finally, don’t forget to perform a DB reindex following the removal of the orphans. As with any other substantial delete (and in a case I saw last week, I was deleting 28.7 million records out of 29.3 million total in the assignment custom values table), you’re going to leave your indexes extremely sparse. To perform the DB reindex, you can execute your normal DB maintenance plan (it has a DB reindex in it, right?), create a custom maintenance plan using the wizard, write your own T-SQL script, or use SSMS to write it for you (expand a target table in Object Explorer, right click on “Indexes” and choose “Rebuild All”. If you’re doing a bunch, I prefer to use the “Script” dropdown  in the top near “Help” to build up 1 large script that I can leave running while I take a walk).

Finally, run your project server test script (or just click around as a sanity check – ensuring to do a backup and restoration of a test project), take a DB backup, send a note to you PSS case owner thanking them for their help, send out a note to your infrastructure buddies bragging about how much space you just freed up (we took our Archive DB from an embarrassing 55GB –> 3GB), and start beginning conversations about deploying the latest service pack/cumulative hotfix in order to prevent this from happening again.

Technorati Tags: Project Server 2007,Project Server 2007 DB,Project Server 2007 Maintenance