Microsoft KB Archive/273665

= PRJ2000: How to Configure Microsoft Project Central to Reduce Deadlock Victim Errors =

Article ID: 273665

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Project 2000 Standard Edition

-



This article was previously published under Q273665



SUMMARY
When you set up Microsoft Project Central to use Microsoft SQL Server 7.0 or the Microsoft Data Engine (MSDE) to store the Microsoft Project Central database, high levels of concurrent use may generate &quot;deadlock victim&quot; errors. This article describes how to modify one of the database Setup scripts, or an existing database, to significantly reduce the occurrence of these errors.

You must modify the Crttable.sql database Setup script before you run the three scripts that create the database. The &quot;More Information&quot; section of this article describes how to modify the Crttable.sql script. For information about how to run the scripts after you modify them, see the &quot;References&quot; section later in this article.

The database administrator can make equivalent changes to an existing database by using SQL Server Enterprise Manager to edit the tables directly, or by entering the appropriate commands in one of the following utilities:
 * Query Analyzer
 * The OSQL or ISQL command-prompt utilities

NOTE: Microsoft strongly recommends that administrators back up the existing database before they make such modifications.

Installation Notes
After you run the modified scripts to create the database, you must choose the Customize option in Setup for Microsoft Project Central, to specify the SQL Server 7 or MSDE database that the scripts created. If you use MSDE, this also means MSDE must be installed before you can run the modified scripts.

If MSDE has not been installed yet and you want to use an MSDE database, you can run Setup for Microsoft Project Central with the Install Now option. Setup installs MSDE, and the default scripts run automatically after the Windows Installer restarts the computer. After Setup is complete, you can modify the Crttable.sql script as described in this article, and then run all three database Setup scripts against the new MSDE database, to replace the default tables that were created.



Scenario 1
Resources attempt to submit actual work in the Timesheet view in Microsoft Project Central, and receive the following error message:

Your transaction (process ID number) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.

This scenario occurs under relatively heavy concurrent use, when multiple users attempt to send a Timesheet update to their project managers at the same, or nearly the same time.

To configure Microsoft Project Central to reduce the occurrence of this error, you must make the following changes to the index configurations for two of the Microsoft Project Central database tables:
 * In the MSP_WEB_MESSAGES table, remove the clustered index for the MESSAGES_MSGID_WMSGRECIPRESID column.
 * In the MSP_WEB_ASSIGNMENTS table, add an index for the WRES_ID_TEAM_LEAD column.

To make these changes to the Crttable.sql script, follow these steps:  Copy the three Microsoft Project Central database Setup scripts from the Microsoft Project 2000 installation CD to an empty folder on the computer where you are installing Microsoft Project Central.

NOTE: The database Setup scripts are located in the \PJCNTRL\ISAPI\1033 folder of the Microsoft Project 2000 installation CD. The three scripts required to set up Microsoft Project Central to use Microsoft SQL Server 7 or MSDE are:

 Crttable.sql Insdefsq.sql Insdefsi.sql  In Microsoft Notepad or another ASCII text editor, open Crttable.sql.  Remove or comment out the following three lines: CREATE UNIQUE CLUSTERED INDEX I_MESSAGES_MSGID_WMSGRECIPRESID ON MSP_WEB_MESSAGES(WMSG_ID, WRES_ID_RECEIVER) WITH FILLFACTOR = 40 GO </li>  Add the following three lines after the comment &quot;-- now for the rest of the indexes --&quot;: CREATE INDEX I_ASSIGN_WRESID_TEAM_LEAD ON MSP_WEB_ASSIGNMENTS(WRES_ID_TEAM_LEAD) GO </li> On the File menu in Microsoft Notepad, click Save. Click Yes when you are asked whether to replace the existing file.</li> On the File menu in Microsoft Notepad, click Exit.</li></ol>

Scenario 2
Resources submit a requested status report in Microsoft Project Central, and receive the following error message:

Your transaction (process ID number) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.

This scenario occurs when two or more users submit a requested status report in Microsoft Project Central at the same, or nearly the same time.

To configure Microsoft Project Central to reduce the occurrence of this error, you must add an index for the WSR_ID column to the MSP_WEB_STATUS_RESPONSES table.

To make the change to the Crttable.sql script, follow these steps: <ol> Copy the three Microsoft Project Central database Setup scripts from the Microsoft Project 2000 installation CD to an empty folder on the computer where you are installing Microsoft Project Central.

NOTE: The database Setup scripts are located in the \PJCNTRL\ISAPI\1033 folder of the Microsoft Project 2000 installation CD. The three scripts required to set up Microsoft Project Central to use Microsoft SQL Server 7 or MSDE are:

 Crttable.sql</li> Insdefsq.sql</li> Insdefsi.sql</li></ul> </li> In Microsoft Notepad or another ASCII text editor, open Crttable.sql.</li>  Add the following three lines at the end of the file, before the comment &quot;-- end of script --&quot;: CREATE INDEX I_WSR_ID ON MSP_WEB_STATUS_RESPONSES(WSR_ID) GO </li> On the File menu in Microsoft Notepad, click Save. Click Yes when you are asked whether to replace the existing file.</li> On the File menu in Microsoft Notepad, click Exit.</li></ol>

<div class="references_section">