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


Locations of visitors to this page
   
  powered by myself. DBA-Sybase 2006