Showing posts with label viadbcc. Show all posts
Showing posts with label viadbcc. Show all posts

Monday, March 26, 2012

Duplicate Identical ROWs PROBLEM in table with unique Clustered Index

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_INF
ORMATION 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 PRIMARYHi,Dean
Ok, what is about sample data?
"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_INF
ORMATION 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
>
>|||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_INF
ORMATION 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
>
>sql