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

dbname – is the name of the database for which to display or set the recovery fault isolation mode. For displaying, the default is the current database. For setting, you must be in the master database and specify the target dbname.
database – marks the entire database suspect, which makes it inaccessible, if the recovery process detects that any of its data is suspect.
page – marks only the corrupt pages suspect, making them inaccessible, if recovery detects corrupt data in the database. The rest of the data is accessible.
read_only – if specified, marks the entire database read only if recovery marks any pages suspect.

Examples

Example 1 – Displays the recovery fault isolation mode for the current database:

sp_setsuspect_granularity

DB Name  Cur. Suspect Gran.  Cfg. Suspect Gran.  Online mode
-------  ------------------  ------------------  -----------
pubs2    database           database           read/write

Example 2 – Displays the current and configured recovery fault isolation mode for the pubs2 database:
```
sp_setsuspect_granularity pubs2
```

Example 3 – The next time recovery runs in the pubs2 database, if any corrupt pages are detected, only the suspect pages are taken offline and the rest of the database is brought online:

sp_setsuspect_granularity pubs2, "page"

DB Name       Cur. Suspect Gran. Cfg. Suspect Gran.
------------- ------------------ -----------------
pubs2          database             database
sp_setsuspect_granularity: The new values will become effective
    during the next recovery of the database ’pubs2’.

Example 4 – The next time recovery runs in the pubs2 database, if any corrupt pages are detected, only the suspect pages are taken offline and the rest of the database is brought online in read only mode:
```
sp_setsuspect_granularity pubs2, "page", "read_only"
```
Example 5 – The next time recovery runs in the pubs2 database, if any corrupt data is detected, the entire database is marked suspect and taken offline:
```
sp_setsuspect_granularity pubs2, "database"
```

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.

Setting	Description
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:

Information	Values
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