Re: Duplicate Identical ROWs PROBLEM in table with unique Clustered Index

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dean Nates (dnatenzon_at_alexandra.net)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 11:11:21 -0500

Here is the actual sample of what DBCC checkdb reported:

 Error: 8908, Severity: 22, State: 6
 Table error: Database ID 6, object ID 1065770854, index ID 0. Chain linkage
mismatch. (1:574284)->next = (1:574285), but (1:574285)->prev = (1:481988 )
.

"Dean Nates" <dnatenzon@alexandra.net> wrote in message
news:uAYMKNrBEHA.2888@TK2MSFTNGP09.phx.gbl...
> Hi !
>
> We are on SQL SERVER 2000 SP3.
>
> We recently had a problem where it appeared (after an extensive reseach
via
> DBCC checkdb)
> we had duplicate identical rows enter a table that had a unique clustered
> index defined on most
> columns in the table. DBCC checkdb kept reporting corrupt index pages .
> After running a number of DBCC checkdb and DBCC reindex the database was
> flagged
> as (Suspect). We correted the (Suspect) problem by re-loading a copy of an
> already corrupt database and deleting duplicate rows (set rowcount 1;
DELETE
> ..).
> After that exersise, DBCC checkdb showed no more problems.
>
> One thing to note, prior to 2000, the create index syntax allowed for the
> following options:
> IGNORE_DUP_ROW and ALLOW_DUP_ROW.
> In 2000, those options were dropped from the syntax ... I wonder if this
> created a hole ....
>
> The question is: Why did the database allowe duplicate rows to enter into
a
> table with
> a UNIQUE CLUSTERED INDEX defined ??
>
> Can anyone offer any help ?? Many thanks !
>
> Here is the table:
> CREATE TABLE dbo.CROSS_REF_DATA_IDENTIFIERS (
> ASSET_ID bigint NOT NULL ,
> ID_CD varchar (3) AS NOT NULL ,
> ID_NUMBER varchar (30) AS NOT NULL ,
> ID_EFFECTIVE_DATE datetime NOT NULL ,
> PROCESSED int NULL ,
>
> CONSTRAINT CROSS_REF_DATA_IDENTIFIERS_PK PRIMARY KEY CLUSTERED
> (ASSET_ID,
> ID_CD,
> ID_NUMBER,
> ID_EFFECTIVE_DATE
> ) ON PRIMARY ,
>
> CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_X_CORE_INFORMATION FOREIGN KEY
> (ASSET_ID) REFERENCES dbo.X_CORE_INFORMATION (ASSET_ID),
>
> CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_CDS_VENDOR_SRC FOREIGN KEY
> (ID_CD) REFERENCES dbo.CDS_VENDOR_SRC ( CODE)
> ) ON PRIMARY
>
>
>
>