Corrupted index

Hi,
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that there
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord = 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
WayneWayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord => 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> > Hi,
> > I have a strange problem with an index on one table. The table has an
> > identity column as its primary key and it is clustered. There are 4
> > other non clustered indexes on the table. When I run a DBCC it shows
> > me that there are several consistency errors regarding one index.
> > The messages all say something like this
> >
> > Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> > Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> > key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> > Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> > Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord => > 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> > 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> > idca_CallRecord = 19026579).
> >
> > When I have seen anything like this in the past I have always just
> > dropped the offending index and recreated it, run DBCC again and
> > everything is consistent. However when I try that on this database
> > the consistency errors are shown immediately again when I run DBCC
> > after the create index. It is only this index that has any
> > consistency problems. The underlying table shows none.
> >
> > Thanks in advance for any help.
> >
> > Wayne
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>