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)
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...
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
Do not create multiple indexes on a table at the same time.
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.