Case study 6: How to solve "suspect index" problem
Platform: HP-UNIX 11, Tru64 , Windows
Sybase ASE version: 12.5.x
Background story: I've encountered error which code is given below:
Index id 5 on table id 2034103256 cannot be used in the optimization of a query as it is SUSPECT. Please have the SA run DBCC REINDEX on the specified table..
Task: See why "suspect index" error occurs and solve problem with suspect indexes.
Solution: First thing which has to be done is to find which tables have suspect indexes on it. It can be done with following command:
sp_indsuspect
It will list all tables and their indexes which are marked as suspect. Take all tables from the list and execute following command for every listed table:
dbcc reindex(tablename)
Remark: I've got this error when trying to make cross platform backup/restore. I am sure there are also other situations which make indexes suspicious
