Microsoft KB Archive/298806

= PRB: Index corruption occurs in a database that is upgraded to SQL Server 2000 =

Article ID: 298806

Article Last Modified on 11/17/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q298806



SYMPTOMS
If you run a DBCC CHECKDB statement on a database that was upgraded from SQL Server 7.0 to SQL Server 2000, the following error message may occur:

Server: Msg 8951, Level 16, State 1, Line 1

Table error: Table '' (ID ). Missing or invalid key in index '' (ID ) for the row:

Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:133:42) identified by (RID = (1:133:42) ) has index values ( = 0).

Server: Msg 8952, Level 16, State 1,

Line 1 Table error: Database '', index '.' (ID ) (index ID ). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:137:0) with values ( = 0) points to the data row identified by (RID = (1:133:42)).



CAUSE
The sysindexes.status field should have a value of 2097152 so that the nonclustered index column can allow the insertion of NULL values for both SQL Server 7.0 and SQL Server 2000.

In SQL Server 7.0, if the sysindexes.status value is 0 for the nonclustered index in a table, the nonclustered index also allows the insertion of NULL values. If you run a DBCC CHECKDB statement on the SQL Server 7.0 database, the problem is not reported. After you upgrade the table in SQL Server 2000, the DBCC CHECKDB statement reports the error message described in the &quot;Symptoms&quot; section because a NULL value is not allowed when the sysindexes.status value is not 2097152.



WORKAROUND
To work around this problem, use any one of the following three methods:
 * Run a DBCC DBREINDEX statement on the SQL Server 2000 table to rebuild the nonclustered index on the column that has a null value inserted.
 * Drop, and then re-create the nonclustered index on the column that is inserted with a null value on the SQL Server 2000 table.
 * Drop, and then re-create the indexes or run a DBCC DBREINDEX statement on SQL Server 7.0 before you upgrade the databases to SQL Server 2000.



MORE INFORMATION
This problem does not cause any data or index corruption. The problem is in the metadata which is corrected only by dropping and re-creating the indexes.

Additional query words: index sysindexes status DBreindex nonclustered

Keywords: kbprb KB298806

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.