All of the indexes corresponding to nonclustered primary keys in my SQL Server 2005 databases appear to be corrupt. Attempts to use DBCC DBREINDEX produce the following error...
Msg 211, Level 23, State 5, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
DBCC CHECKCATALOG and DBCC CHECKDB do not find any problems.
I was able to rebuild the indexes prior to upgrade from SQL Server 2000 Standard to SQL 2005 Standard. It was not an "in-place upgrade", we moved database backups to the new server.
I have been able to work around the problem in my development environment by dumping the records to a temp table, dropping the table and then reinserting all the records. Dropping the primary key/index alone will not work... i get the following error...
Msg 3728, Level 16, State 1, Line 3
'PK__mykey' is not a constraint.
Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.
I only noticed this because I was setting up a maintenance task to rebuild indexes occasionally and it failed. I am not noticing any performance issues.
I can survive by dropping and recreating the tables as I mentioned above... but if someone has a better solution I would certainly appreciate it. I have many tables to repair across multiple databases and have to worry about causing downtime for my users.
the primary keys are identity fields
the database server and database operate in 'Latin1_General_BIN' collation.
i am pretty certain this is not a hardware issue; i have restored a backup to 3 seperate sql server machines and the problem persists across them all