Error message when you rebuild an index for a table in a BizTalk Server database: "Cannot insert duplicate key row in object"

View products that this article applies to.

SYMPTOMS

You rebuild the index for a table in a Microsoft BizTalk Server database that was implemented by using Microsoft SQL Server 2005 Service Pack 1 (SP1). After you do this, an error message that resembles the following may be logged in the Application log on the computer that is running BizTalk Server:

Cannot insert duplicate key row in object 'dbo.table name' with unique index 'index name'

In this error message, table name may be one of the following:

TrackingMessageReferences
MessageParts
MessageProps
UniqueSubscription
ConvoySetInstances
Modules

In this error message, index name may be one of the following:

CIX_TrackingMessageReferences
CIX_MessageParts
CIX_MessageProps
IX_UniqueSubscription
CIX_ConvoySetInstances
IX_Modules

Back to the top

CAUSE

This issue may occur if the following conditions are true:

Tracking is enabled for a send port or for a receive port.
You experience the problem that is described in the following Microsoft Knowledge Base article:
936894 (http://support.microsoft.com/kb/936894/ ) FIX: Indexes that at rebuilt after you run a maintenance plan that contains the Rebuild Index task have the same Ignore Duplicate Key setting as the Ignore Duplicate Key setting of the Rebuild Index task

Back to the top

RESOLUTION

To resolve this issue, enable the Ignore duplicate values option for the index. To enable the Ignore duplicate values option, follow these steps:

Start Microsoft SQL Server Management Studio.
Expand the SQL Server installation that you want, expand Databases, expand BizTalkMsgBoxDb, expand Tables, expand the table that is referenced in the error message, and then expand Indexes.
Right-click the index that is referenced in the error message, and then click Properties.
Click Options, click to select the Ignore duplicate values check box, and then click OK.

Back to the top

MORE INFORMATION

To verify that the Ignore duplicate values option is enabled on the index, use the sp_help Transact-SQL command. For example, use the following query to check whether the Ignore duplicate values option is disabled on the CIX_TrackingMessageReferences index in the dbo.TrackingMessageReferences table.

USE BizTalkMsgBoxDb 
EXEC sp_helpindex TrackingMessageReferences; -- CIX_TrackingMessageReferences on TrackingMessageReferences
GO
EXEC sp_helpindex MessageParts; -- CIX_MessageParts on MessageParts
GO
EXEC sp_helpindex MessageProps; -- CIX_MessageProps on MessageProps
GO
EXEC sp_helpindex UniqueSubscription; -- IX_UniqueSubscription on UniqueSubscription
GO
EXEC sp_helpindex ConvoySetInstances; -- CIX_ConvoySetInstances on ConvoySetInstances
GO
EXEC sp_helpindex Modules; -- IX_Modules on Modules
GO

Verify that the Index_description column for the indexes contains ignore duplicate keys.

To avoid this problem, make sure that you update SQL Server 2005 to the latest service pack before you run the re-index operations again. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

913089 (http://support.microsoft.com/kb/913089/ ) How to obtain the latest service pack for SQL Server 2005

Back to the top

REFERENCES

For more information about the issues that may occur when you connect to the BizTalkMsgBox Db database, click the following article number to view the article in the Microsoft Knowledge Base:

917845 (http://support.microsoft.com/kb/917845/ ) You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004

Back to the top