Publishing is blocked when you publish large project plans in a deployment of Project Server 2003 that uses more than one server that is running the Views Notification service (924519)


The information in this article applies to:

  • Microsoft Office Project Server 2003

SYMPTOMS

Consider the following scenario. In your deployment of Microsoft Office Project Server 2003, you have more than one server that is running the Views Notification service. You publish multiple large project plans to Project Server 2003. However, after some time, you can no longer publish project plans. Publishing is blocked. Specifically, the following query is blocked:

MSP_WEB_SP_QRY_ViewTaskTimephasedByDay_DeleteViewTable0;1

You experience this symptom intermittently.

CAUSE

This issue occurs if the following conditions are true:
  • You have multiple views processing servers in your deployment of Project Server 2003.
  • You publish multiple large project plans.
  • You use the Microsoft Office Project 2003 Software Development Kit (SDK) to extend the databases that are used by Project Server 2003 to include the MSP_VIEW_PROJ_RES_TP_BY_WEEK table.
  • A deadlock condition occurs in the MSP_VIEW_PROJ_RES_TP_BY_WEEK table.

WORKAROUND

To work around this issue, add an index to the MSP_VIEW_PROJ_RES_TP_BY_WEEK table. To do this, follow these steps:
  1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. In the Connect to SQL Server dialog box, specify the name of the instance of SQL Server to which you want to connect in the SQL Server box, specify your login name and password in the Login name and Password boxes, and then click OK.
  3. On the Query menu, click Change Database.
  4. In the Select database of ServerName dialog box, click ProjectServer, and then click OK.
  5. Copy and then paste the following lines to the Query box.
    alter table MSP_VIEW_PROJ_RES_TP_BY_WEEK add SUROGATE_KEY int identity constraint SUROGATE_KEY_PK PRIMARY KEY
go
create nonclustered index TESTINDEX1 on MSP_VIEW_PROJ_RES_TP_BY_WEEK ( WPROJ_ID )
go
  6. On the Query menu, click Execute. You receive the following message in the Messages box: The command(s) completed successfully.
  7. Exit SQL Query Analyzer.
Modification Type:MajorLast Reviewed:10/3/2006
Keywords:kbPerformance kbExpertiseInter kbtshoot kbprb KB924519 kbAudKnowledgeWorker
©2004 Microsoft Corporation. All rights reserved.