FIX: Creating Indexes in Parallel May Fail with Deadlock on Sysindexes

This article was previously published under Q295373
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 236561 (SHILOH_BUGS)
SYMPTOMS
Creating multiple indexes at the same time on a table may fail with a deadlock on sysindexes. One of the CREATE INDEX statements will fail with the following error:
Server: Msg 1205, Level 13, State 3, Line 1
Your transaction (process ID #8) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
Trace flag 1204 can be used to capture deadlock information to the SQL Server errorlog. You can identify a deadlock on sysindexes by a KEY value of xx:2:1 (where xx is the database ID) in the errorlog:

Deadlock encountered .... Printing deadlock information2000-10-02 11:51:39.82 spid4     Wait-for graph2000-10-02 11:51:39.82 spid42000-10-02 11:51:39.82 spid4     Node:12000-10-02 11:51:39.82 spid4     KEY: 5:2:1 (60000b20631c)      CleanCnt:1 Mode: X Flags: 0x02000-10-02 11:51:39.82 spid4     Grant List::2000-10-02 11:51:39.82 spid4     Owner:0x19171080 Mode: X       Flg:0x0 Ref:0 Life:02000000 SPID:56 ECID:02000-10-02 11:51:39.82 spid4     SPID: 56 ECID: 0 Statement Type: CREATE INDEX Line #: 5...2000-10-02 11:51:39.82 spid4     Requested By:2000-10-02 11:51:39.82 spid4     ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x19447a58) Value:0x191705c0 Cost:(0/0)2000-10-02 11:51:39.82 spid4     2000-10-02 11:51:39.82 spid4     Node:22000-10-02 11:51:39.82 spid4     KEY: 5:2:1 (61004a117805)      CleanCnt:1 Mode: X Flags: 0x02000-10-02 11:51:39.82 spid4     Grant List::2000-10-02 11:51:39.82 spid4     Owner:0x1916f540 Mode: X       Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:02000-10-02 11:51:39.82 spid4     SPID: 55 ECID: 0 Statement Type: CREATE INDEX Line #: 5...				
RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
WORKAROUND
Do not create multiple indexes on a table at the same time.
STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
Properties

Article ID: 295373 - Last Review: 01/16/2015 22:21:49 - Revision: 3.2

  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB295373
Feedback