Microsoft KB Archive/295373

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

Article ID: 295373

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q295373



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 information 2000-10-02 11:51:39.82 spid4    Wait-for graph 2000-10-02 11:51:39.82 spid4 2000-10-02 11:51:39.82 spid4    Node:1 2000-10-02 11:51:39.82 spid4    KEY: 5:2:1 (60000b20631c)      CleanCnt:1 Mode: X Flags: 0x0 2000-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:0 2000-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:2 2000-10-02 11:51:39.82 spid4    KEY: 5:2:1 (61004a117805)      CleanCnt:1 Mode: X Flags: 0x0 2000-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:0 2000-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 the Microsoft 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.

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB295373

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.