Lists all indexes in a database that are currently offline because of corruption detected on recovery.


sp_listsuspect_object [dbname]




There are additional considerations when using sp_listsuspect_object:
  • 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. Permission checks do not differ based on the granular permissions settings.


Values in event and extrainfo columns from the sysaudits table are:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference