Recovering from Project Server corruption

by Martin Laukkanen | May 10, 2012 | Project 2010, Troubleshooting

A customer of mine recently had to recover from a worst case corruption scenario; one in which reverting to a backup was not an option. In short a hardware change resulted in progressive database corruption that was not picked up for over two weeks(!), leaving few options but to attempt to recover the corruption in-place.

Firstly though we had to fix the SQL databases, for that the following command was needed;

alter database ProjectServer_Published SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB(‘ProjectServer_Published’, REPAIR_ALLOW_DATA_LOSS)

alter database ProjectServer_Published SET MULTI_USER;

Yes that parameter does say “Allow Data Loss”, once you run this there is no going back, the databases are now free of consistency errors and other corruptions but in order to reach that state the DBCC command probably had to delete data!

So what now?

Well (fortunately for once!!) Project Server and Project Professional both are no stranger to application level corruption (*cough* 2007 *cough*), so despite DBCC repairing 7000+ consistency errors in the published database alone we are not without options.

So let’s look at some of the errors we had to deal with, firstly the MS Project client side errors and how we dealt with them.

MS Project Errors

Error 1. There is a circular relationship in task …

This one was surprising, but a sign that most likely the last save attempt for this particular schedule wrote corruption to some of the tasks.

To correct this you need to identify the task and correct or delete the predecessors, in my experience the circular reference is pretty obvious so fixing it is quick, however if that’s not possible then I’ve previously found this procedure helpful: http://www.domorethanmanage.com/articles/2008/05/29/Resolvingcircularreferenc.html

Error 2. Save Error’s 9000(0x2328) and 26005(0x6595)

I’ve put these two together as the solution is the same, in fact this procedure also applies to the Publish errors that you may get also see.

If you see the above, the first thing to try is an administrative restore, however if that fails there is one last resort;

The “XML Round Trip” method:

1. Open the project in MS Project.

2. File, Save As, Save As File.

3. When prompted select to save with “All Enterprise Custom Fields”.

4. In the file dialog select the Save as type: XML

5. Save the file to your local computer.

6. Once saved, close the project (don’t forget to check in!) and reopen the saved XML file, when prompted choose to import ‘As a new project’.

7. Once opened, select Save As and save the project to the project server using the exact same project name.

8. When prompted say yes to overwrite the existing project (you did check it in right?)

9. Publish and your done.

Note; This process will overwrite any previously saved data for that project, the impacts of this are as follows;

• In-progress timesheets will be affected, remember that internally all tasks and assignments are recreated, so existing OPEN timesheet periods will be updated. This may result in un-submitted timesheets losing actual work, or worse in progress timesheets failing to submit.
• Reports or custom add-in’s relying on GUID’s will likely have issues.

Error 3: Unexpected problem occurred while opening the file

If you see this, then hopefully one of the following works, if not you might be out of luck!

• Open the project from the local project cache.
• Open the PUBLISHED version of the project by selecting the appropriate Store from the open dialog.

• Restore the project from administrative backup.

PWA Errors

Next, a number of errors in PWA can show up as a result of corruption, it’s worth mentioning that pretty much all of the data in PWA comes solely from the Published database. This is good to know because typically 99% of them can be fixed by simply re-publishing!

That’s all good when dealing with a single project, however when your dealing with multiple project corruption, or simply more commonly when a view fails and you don’t know *what* project caused it this method of bulk-publishing will come in handy:

Bulk Publishing Projects using ProjTool

Firstly get yourself ProjTool 2010: http://blogs.msdn.com/b/project_programmability/archive/2010/11/03/projtool-for-project-server-2010.aspx

This tool is extremely powerful (IE destructive!) and should be used with caution, but just completing these steps you don’t need to get too deep into the tool so your not risking too much:

1. Open ProjTool, enter project server URL and select Windows for Authentication.

2. Select the projects to republish (multi-select by holding shift).

3. Click Publish from the Toolbar, when prompted select YES for a FULL Publish.

From experience selecting over a couple of hundred projects will cause this to fail, but I have often used this to queue up 80+ projects for a republish.

Note however that almost ALWAYS ProjTool will report a problem in the queue, that’s because it only waits for a fixed time period for the queue jobs to complete and that never seems to be enough.

PWA View Errors

(The view failed to load. Press OK to reload…)

(An unknown error has occurred.)

These types of errors are likely caused by one of the following:

1. Corruption in the project data being viewed
2. Basic corruption in the view configuration.

For #1 this can be a custom field value that is filtered where one project has an invalid (null?) value for the field in question, so often just removing any filters configured on the view will fix this.

If you identify the filter in question then see the tips above about republishing (or possibly re-saving AND re-publishing) some or all projects to correct the data.

For #2 there are few options other than to recreate the view, copying the current view sometimes helps but not reliably.

Project Workspace Permissions Issues

I’m not going to cover how to restore document or general SharePoint data corruption in this blog, but in terms of Project Server usage you’ll most likely have issues relating to to the project permissions synchronisation, for this see the following link: https://nearbaseline.com/blog/2011/02/resetting-lost-permissions-in-project-server-2010/

Other Issues

Duplication of Custom Field values in PDPs:

This one turned out to be just an odd co-incidence in timing see the following recent Microsoft kb article if you see this issue;

http://support.microsoft.com/kb/2598251

Finally Reporting Database Refresh

Once all the above has been corrected you may still see some errors in Reporting jobs in the queue;

ReportingProjectChangeMessageFailed (24006) – The INSERT statement conflicted with the FOREIGN KEY constraint

There are a few options available for this problem, two are well described here: http://www.epmpartners.com.au/blog/insert-statement-conflicted-with-the-foreign-key-constraint/

I’d suggest the RDB Refresh (Option 2 in the blog linked), this will take some time (hours typically), and any failures will result in projects NOT being listed in the reporting database at all. This is because the job removes and resynchronises the projects, so typically after running this job I usually use ProjTool to do a bulk publish (see above).

Final Words

The moral of this story is simple: Make sure your SQL DBA regularly uses DBCC CHECKDB (http://www.sql-server-pro.com/dbcc-checkdb.html) to ensure you don’t get into this sort of mess!