Lists all indexes in a database that are currently offline because of corruption detected on recovery.
sp_listsuspect_object [dbname]
is the name of the database.
Lists the suspect indexes in the current database:
sp_listsuspect_object
Lists the suspect indexes in the pubs2 database:
sp_listsuspect_object pubs2
If an index on a data-only-locked table has suspect pages, the entire index is taken offline during recovery. Offline indexes are not considered by the query optimizer.
Use the system procedure sp_forceonline_object to bring an offline index online for repair.
Indexes on allpages-locked tables are not taken completely offline during recovery; only individual pages of these indexes are taken offline. These pages can be brought online with sp_forceonline_object.
sp_listsuspect_object lists the database name, object ID, object name, index ID, and access status for every suspect index in the specified database or, if dbname is omitted, in the current user database.
A value of SA_ONLY in the access column means that the index has been forced online for System Administrator use only. A value of BLOCK_ALL means that the index is offline for everyone.
See the System Administration Guide for more information on recovery fault isolation.
Any user can execute sp_listsuspect_object.
System procedures sp_forceonline_object
