Displays or sets the recovery fault isolation mode for a user database, which governs how recovery behaves when it detects data corruption.
sp_setsuspect_granularity [dbname [, "database" | "page" [, "read_only"]]]
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.
marks the entire database suspect, which makes it inaccessible, if the recovery process detects that any of its data is suspect.
marks only the corrupt pages suspect, making them inaccessible, if recovery detects corrupt data in the database. The rest of the data is accessible.
if specified, marks the entire database read only if recovery marks any pages suspect.
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
Displays the current and configured recovery fault isolation mode for the pubs2 database:
sp_setsuspect_granularity pubs2
The next time recovery runs in the pubs2 database, if any corrupt pages are detected, only the suspect pages will be taken offline and the rest of the database will be 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’.
The next time recovery runs in the pubs2 database, if any corrupt pages are detected, only the suspect pages will be taken offline and the rest of the database will be brought online in read only mode:
sp_setsuspect_granularity pubs2, "page", "read_only"
The next time recovery runs in the pubs2 database, if any corrupt data is detected, the entire database will be marked suspect and taken offline:
sp_setsuspect_granularity pubs2, "database"
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. Se 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 Adaptive 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_dband 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, Adaptive 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 will be 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.
The permission checks for sp_setsuspect_granularity differ based on your granular permissions settings.
Granular permissions 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. |
Granular permissions 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. |
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 |
|
Commands dump database, dump transaction, load database
System procedures sp_dboption, sp_forceonline_db, sp_forceonline_page, sp_listsuspect_db, sp_listsuspect_page, sp_setsuspect_threshold
