Project Server 2007: Upgrading from a Pre October CU may fail during PSConfig step 8

With Project Server 2007 SP2 just around the corner many of you will hopefully be upgrading over the next few weeks.  As with any upgrade you should always test first in a non-production environment but preferably with a copy of your production data.  We are aware of an issue that can occur when upgrading from a server that has not been patched since before the October CU to any level from October up to SP2 and even the April 2009 CU (which will be available around the time SP2 ships too).  We are working on a resolution for this problem and depending when you are reading this we may already have a solution.  If you run into this issue during your testing then please log a support incident.

The details of the failure are that the SharePoint Configuration Wizard will fail at step 8 while it is updating the databases and the error logged to the upgrade and ULS log will look something like:

The DELETE statement conflicted with the REFERENCE constraint “FK_MSP_TimesheetLine_TaskNameUID”. The conflict occurred in database “TaskConsistency_<instance_name>_Reporting”, table “dbo.MSP_TimesheetLine”, column ‘TaskNameUID’.

The statement has been terminated.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()   at Microsoft.Office.Server.Data.SqlSession.ExecuteScript(TextReader textReader, Int32 commandTimeout)   at Microsoft.SharePoint.Upgrade.SPDatabaseAction.ExecuteSql(String sql, Int32 commandTimeout)   at Microsoft.SharePoint.Upgrade.SPDatabaseAction.ExecuteSql(String sql)at Microsoft.Office.Project.Server.Upgrade.ReportingDatabaseUpgrader_12_1_153_0.Upgrade()   at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade()

with the key parts of the message being:

Microsoft.Office.Project.Server.Upgrade.ReportingDatabaseUpgrader_12_1_153_0.Upgrade()

You will also notice the Versions table in the reporting database will look something like this:

VersionId Version Id
00000000-0000-0000-0000-000000000000 12.0.4518.1016 2
00000000-0000-0000-0000-000000000000 12.0.6218.1000 3
00000000-0000-0000-0000-000000000000 12.0.6300.5000                                             4
00000000-0000-0000-0000-000000000000 12.0.6318.5000 5
00000000-0000-0000-0000-000000000000 12.0.6327.5000 6
A1EF27C5-A62C-44A2-BEA8-D244C622A092 12.1.152.0 1

The first few lines will depend on the various upgrades previously loaded, but the final line signifies that the last successful upgrade step was 152, and the 12_1_153 failed.

As I mentioned, we are working on a resolution for this problem but wanted to be sure you can either find this information if you have already run into the problem, or consider it as part of your planning process for your upgrade – and making sure you test upgrade your production data in a non-production environment.

From our experience we suspect this issue to only affect a very small percentage of our customers (we have only seen the problem once in the wild) but we do appreciate, that if you happen to be one of this small percentage then this is no comfort.  Log a support call and we will help you through this.

Thanks to Aik for his great work on this one!