Index Error

Hello,
We are running SQL 2000 Service Pack 3a.
We are getting the following error when we perform a load..
Error: 8646, Severity: 21, State: 1
The index entry for row ID was not found in index ID 5, of table 933578364,
in database 'MYDBNAME'.
I have run a DBCC CHECK db on the database and it reported no errors.
I have also followed the instructions in
http://support.microsoft.com/kb/822747 however we do not have a unicode
field, and the collation is different.
We backed up the Database and put it onto a different Server and the error
occured again.
The table it points to has just over 1 million records.
Now here is the oddity, if I delete the indexes and put them back in I can
run the load with the same data and it works.
I am at a loss on how to proceed with this, so I was wondering if some
bright people could help.
Thanks
J
The schema for the table is as follows :-
CREATE TABLE [dbo].[PARCELS] (
[PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CONSIGNMENT_NO] [int] NULL ,
[RETL_CODE] [smallint] NULL ,
[PARCEL_TYPE] [tinyint] NOT NULL ,
[PARCEL_NO] [int] NULL ,
[BOX_NUMBER] [smallint] NULL ,
[BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[CREATED_AT] [bigint] NULL ,
[CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[SIZE] [int] NOT NULL ,
[WEIGHT] [int] NOT NULL ,
[CUSTOMER_CONSIGNMENT] [int] NULL ,
[LAST_TRACK_EVENT] [int] NULL ,
[HELD] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[PARCEL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] ADD
CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
GO
CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSI
GNMENT]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON &
#91;PRIMARY]
GO
CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO
]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE])
ON [PRIMARY]
GOJulie,
Sounds like a questionable nonclustered index. Since the index doesn't
contain the actual data but pointers to the data the quick fix would
probably be to just drop the index and recreate it. If it occurs again, you
might check the disk (array) if DBCC CHECDB is not reporting anything.
HTH
Jerry
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:813D3A08-56FD-41A8-B307-C66963DCBC40@.microsoft.com...
> Hello,
> We are running SQL 2000 Service Pack 3a.
> We are getting the following error when we perform a load..
> Error: 8646, Severity: 21, State: 1
> The index entry for row ID was not found in index ID 5, of table
> 933578364,
> in database 'MYDBNAME'.
> I have run a DBCC CHECK db on the database and it reported no errors.
> I have also followed the instructions in
> http://support.microsoft.com/kb/822747 however we do not have a unicode
> field, and the collation is different.
> We backed up the Database and put it onto a different Server and the error
> occured again.
> The table it points to has just over 1 million records.
> Now here is the oddity, if I delete the indexes and put them back in I can
> run the load with the same data and it works.
> I am at a loss on how to proceed with this, so I was wondering if some
> bright people could help.
> Thanks
> J
> The schema for the table is as follows :-
> CREATE TABLE [dbo].[PARCELS] (
> [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CONSIGNMENT_NO] [int] NULL ,
> [RETL_CODE] [smallint] NULL ,
> [PARCEL_TYPE] [tinyint] NOT NULL ,
> [PARCEL_NO] [int] NULL ,
> [BOX_NUMBER] [smallint] NULL ,
> [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> [CREATED_AT] [bigint] NULL ,
> [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [SIZE] [int] NOT NULL ,
> [WEIGHT] [int] NOT NULL ,
> [CUSTOMER_CONSIGNMENT] [int] NULL ,
> [LAST_TRACK_EVENT] [int] NULL ,
> [HELD] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [PARCEL_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] ADD
> CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> GO
> CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE])
ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CON
SIGNMENT]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON
[PRIMARY]
> GO
> CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO])
ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_
NO]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]
) ON [PRIMARY]
> GO
>
>