sp_setsuspect_granularity

Displays or sets the recovery fault isolation mode for a user database, which governs how recovery behaves when it detects data corruption.

Syntax

sp_setsuspect_granularity [dbname 
	[, "database" | "page" [, "read_only"]]]

Parameters

Examples

Usage

There are additional considerations when using sp_setsuspect_granularity:
  • sp_setsuspect_granularity displays and sets the recovery fault isolation mode. This mode governs whether recovery marks an entire database or only the corrupt pages suspect when it detects that any data that it requires has been corrupted. See the System Administration Guide for more information.

  • The default recovery fault isolation mode of a user database is “database”. You can set the recovery fault isolation mode only for a user database, not for a system database.

  • The Cluster Edition allows only the database option with sp_setsuspect_granularity.

  • You must be in the master database to set the recovery fault isolation mode.

  • Data marked suspect due to corruption persists across SAP ASE server start-ups. When certain pages have been marked suspect, they remain offline after you reboot the server.

  • When part or all of a database is marked suspect, the suspect data is not accessible to users unless a system administrator has made the suspect data accessible with the sp_forceonline_db and sp_forceonline_page procedures.

  • General database corruption, such as a corrupt database log or the unavailability of another resource not specific to a page, causes the entire database to be marked suspect, even if the recovery fault isolation mode is “page”.

  • If you do not specify page or database, the SAP ASE server displays the current and configured settings. The current setting is the one that was in effect the last time recovery was executed in the database. The configured setting is the one that is in effect the next time recovery is executed in the database.

  • If the database comes online in read_only mode, no user can modify any of its data, including data that is unaffected by the suspect pages and is thus online. However, the system administrator can make the database writeable using the sp_dboption system procedure to set read only to false. In this case, users could then modify the online data, but the suspect data would remain inaccessible.

See also dump database, dump transaction, load database in Reference Manual: Commands.

Permissions

The permission checks for sp_setsuspect_granularity differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with own database privilege on the specified database to set the recovery fault isolation mode.

Any user can execute sp_setsuspect_granularity to display settings.

Disabled

With granular permissions disabled, you must be a user with sa_role to set the recovery fault isolation mode.

Any user can execute sp_setsuspect_granularity to display settings.

Auditing

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

InformationValues
Event

38

Audit option

exec_procedure

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
sp_dboption
sp_forceonline_db
sp_forceonline_page
sp_listsuspect_db
sp_listsuspect_page
sp_setsuspect_threshold