Index Error - Re-Post

Apologies for the re-post...
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'.
The program that runs the load process is a Java program.
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, but the error occurs after a
couple of loads.
Also if we back up the database and put it onto a completely different box
we get the same error.
So I do not think its a problem with the physical box so it must be
something to do with one of the non clustered indexes.
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_CONSIGNMENT]) 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]
GOHi
When you say you run the load does that mean you are adding/deleting a
significant amount of data? In which case dropping/recreating (or at least
defragging) the indexes would help performance.
The article you refer to was fixed in SP3a therefore would not be applicable.
Have you tried updating statistics
Also you may want to try running the system without the offending index.
John
"Julie" wrote:
> Apologies for the re-post...
> 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'.
> The program that runs the load process is a Java program.
> 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, but the error occurs after a
> couple of loads.
> Also if we back up the database and put it onto a completely different box
> we get the same error.
> So I do not think its a problem with the physical box so it must be
> something to do with one of the non clustered indexes.
> 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_CONSIGNMENT]) 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
>|||Thank you for you post.
It is more that we are updating rather than adding or deleting, however we
use a lot of joining for other tables to perform the update. The table itself
only contains about 1 million records.
We tried to simply remove the indexes however that caused major locking
problems so we had to put them back in. Therefore we cannot drop and recreate
them. We cannot determine which Index is causing the problem as the query
that references the table uses all of them.
Deleting the indexes and put them back in before the load seems to work once
or twice, then the same problem occurs.
We have also tried updating the statisitcs, however that did not work at all.
Thanks for you help
Julie
"John Bell" wrote:
> Hi
> When you say you run the load does that mean you are adding/deleting a
> significant amount of data? In which case dropping/recreating (or at least
> defragging) the indexes would help performance.
> The article you refer to was fixed in SP3a therefore would not be applicable.
> Have you tried updating statistics
> Also you may want to try running the system without the offending index.
> John
> "Julie" wrote:
> > Apologies for the re-post...
> >
> > 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'.
> >
> > The program that runs the load process is a Java program.
> >
> > 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, but the error occurs after a
> > couple of loads.
> >
> > Also if we back up the database and put it onto a completely different box
> > we get the same error.
> >
> > So I do not think its a problem with the physical box so it must be
> > something to do with one of the non clustered indexes.
> >
> > 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_CONSIGNMENT]) 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
> >|||Hi Julie
The index should be:
SELECT *
FROM sysindexes
WHERE id = 933578364
AND indid = 5
John
"Julie" wrote:
> Thank you for you post.
> It is more that we are updating rather than adding or deleting, however we
> use a lot of joining for other tables to perform the update. The table itself
> only contains about 1 million records.
> We tried to simply remove the indexes however that caused major locking
> problems so we had to put them back in. Therefore we cannot drop and recreate
> them. We cannot determine which Index is causing the problem as the query
> that references the table uses all of them.
> Deleting the indexes and put them back in before the load seems to work once
> or twice, then the same problem occurs.
> We have also tried updating the statisitcs, however that did not work at all.
> Thanks for you help
> Julie
>
> "John Bell" wrote:
> > Hi
> >
> > When you say you run the load does that mean you are adding/deleting a
> > significant amount of data? In which case dropping/recreating (or at least
> > defragging) the indexes would help performance.
> >
> > The article you refer to was fixed in SP3a therefore would not be applicable.
> >
> > Have you tried updating statistics
> >
> > Also you may want to try running the system without the offending index.
> >
> > John
> >
> > "Julie" wrote:
> >
> > > Apologies for the re-post...
> > >
> > > 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'.
> > >
> > > The program that runs the load process is a Java program.
> > >
> > > 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, but the error occurs after a
> > > couple of loads.
> > >
> > > Also if we back up the database and put it onto a completely different box
> > > we get the same error.
> > >
> > > So I do not think its a problem with the physical box so it must be
> > > something to do with one of the non clustered indexes.
> > >
> > > 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_CONSIGNMENT]) 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
> > >|||Thanks John
"John Bell" wrote:
> Hi Julie
> The index should be:
> SELECT *
> FROM sysindexes
> WHERE id = 933578364
> AND indid = 5
>
> John
> "Julie" wrote:
> > Thank you for you post.
> >
> > It is more that we are updating rather than adding or deleting, however we
> > use a lot of joining for other tables to perform the update. The table itself
> > only contains about 1 million records.
> >
> > We tried to simply remove the indexes however that caused major locking
> > problems so we had to put them back in. Therefore we cannot drop and recreate
> > them. We cannot determine which Index is causing the problem as the query
> > that references the table uses all of them.
> >
> > Deleting the indexes and put them back in before the load seems to work once
> > or twice, then the same problem occurs.
> >
> > We have also tried updating the statisitcs, however that did not work at all.
> >
> > Thanks for you help
> >
> > Julie
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > When you say you run the load does that mean you are adding/deleting a
> > > significant amount of data? In which case dropping/recreating (or at least
> > > defragging) the indexes would help performance.
> > >
> > > The article you refer to was fixed in SP3a therefore would not be applicable.
> > >
> > > Have you tried updating statistics
> > >
> > > Also you may want to try running the system without the offending index.
> > >
> > > John
> > >
> > > "Julie" wrote:
> > >
> > > > Apologies for the re-post...
> > > >
> > > > 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'.
> > > >
> > > > The program that runs the load process is a Java program.
> > > >
> > > > 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, but the error occurs after a
> > > > couple of loads.
> > > >
> > > > Also if we back up the database and put it onto a completely different box
> > > > we get the same error.
> > > >
> > > > So I do not think its a problem with the physical box so it must be
> > > > something to do with one of the non clustered indexes.
> > > >
> > > > 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_CONSIGNMENT]) 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
> > > >