PRJ2000: How to Configure Microsoft Project Central to Reduce Deadlock Victim Errors (273665)


The information in this article applies to:

  • Microsoft Project 2000
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 "deadlock victim" 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 "More Information" 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 "References" 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.

MORE INFORMATION

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:
  1. 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
  2. In Microsoft Notepad or another ASCII text editor, open Crttable.sql.
  3. 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
  4. Add the following three lines after the comment "-- now for the rest of the indexes --":
    CREATE INDEX I_ASSIGN_WRESID_TEAM_LEAD
ON MSP_WEB_ASSIGNMENTS(WRES_ID_TEAM_LEAD)
GO
  5. On the File menu in Microsoft Notepad, click Save. Click Yes when you are asked whether to replace the existing file.
  6. On the File menu in Microsoft Notepad, click Exit.

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:
  1. 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
  2. In Microsoft Notepad or another ASCII text editor, open Crttable.sql.
  3. Add the following three lines at the end of the file, before the comment "-- end of script --":
    CREATE INDEX I_WSR_ID
ON MSP_WEB_STATUS_RESPONSES(WSR_ID)
GO
  4. On the File menu in Microsoft Notepad, click Save. Click Yes when you are asked whether to replace the existing file.
  5. On the File menu in Microsoft Notepad, click Exit.

REFERENCES

For more information about how to set up and configure Microsoft Project Central Server, including how to run the database Setup scripts, review the Svrsetup.htm file. This file is located in the \PJCNTRL\HELP\1033 folder on the Microsoft Project 2000 CD, and in the \ProjectCentral\Help\1033 folder on the Microsoft Project Central server.

Additional information and tools to set up and administer Microsoft Project Central are available online in the Microsoft Project 2000 Resource Kit. Click the following link to view the Resource Kit on the Microsoft Web site:

Microsoft Project 2000 Resource Kit

Modification Type:MajorLast Reviewed:10/17/2002
Keywords:kbhowto KB273665
©2002 Microsoft Corporation. All rights reserved.