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

Article translations Article translations
Article ID: 295373 - View products that this article applies to.
This article was previously published under Q295373
BUG #: 236561 (SHILOH_BUGS)
Expand all | Collapse all

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.

Properties

Article ID: 295373 - Last Review: November 6, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB295373

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com