Chapter 1: System Procedures

sp_forceonline_object

Provides access to an index previously marked suspect by recovery.

sp_forceonline_object dbname, objname, indid,
	{sa_on | sa_off | all_users} [, no_print]

dbname: is the name of the database containing the index to be brought online.

sa_on: allows only users with the sa_role to access the specified index.

sa_off: revokes access privileges created by a previous invocation of sp_forceonline_object with sa_on.

no_print: skips printing a list of other suspect objects after the specified object is brought online.

Allows a System Administrator to access the index with indid 3 on the titles table in the pubs2 database:

sp_forceonline_object pubs2, titles, 3 , sa_on

Revokes access to the index from the System Administrator. Now, no one has access to this index:

sp_forceonline_object pubs2, titles, 3, sa_off

Allows all users to access the index on the titles table in the pubs2 database:

sp_forceonline_object pubs2, titles, 3, all_users

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. 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_page.
Use sp_listsuspect_object to see a list of databases that are offline.
To repair a suspect index, use sp_forceonline_object with sa_on access. Then, drop and re-create the index.

If the index is on systabstats or sysstatistics (the only data-only-locked system tables) call Sybase Technical Support for assistance.
sp_forceonline_object with all_users cannot be reversed. When an index has been brought online for all users, you cannot take it offline again.
An index that is forced online is not necessarily repaired. Corrupt indexes can be forced online. Adaptive Server does not perform any consistency checks on indexes that are forced online.
sp_forceonline_object cannot be used in a transaction.
sp_forceonline_object works only for databases in which the recovery fault isolation mode is “page.” Use sp_setsuspect_granularity to display the recovery fault isolation mode for a database.
To bring all of a database’s offline pages and indexes online in a single command, use sp_forceonline_db.

Only a System Administrator can execute sp_forceonline_object.

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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