sp_setsuspect_threshold

Description

Displays or sets the maximum number of suspect pages that Adaptive Server allows in a database before marking the entire database suspect.

Syntax

sp_setsuspect_threshold [dbname [, threshold]]

Parameters

dbname

is the name of the database for which you want to display or set the suspect escalation threshold. The default is the current database.

threshold

indicates the maximum number of suspect data pages that recovery will allow before marking the entire database suspect. The default is 20 pages. The minimum is 0.

Examples

Example 1

Sets the maximum number of suspect pages to 5. If there are more than 5 suspect pages, recovery will mark the entire database suspect:

sp_setsuspect_threshold pubs2, 5

Example 2

Displays the current and configured settings for the suspect escalation threshold for the pubs2 database:

sp_setsuspect_threshold pubs2

Example 3

Displays the current and configured settings for the recovery fault isolation threshold for the current user database:

sp_setsuspect_threshold

Usage

Permissions

Only a system administrator can execute sp_setsuspect_threshold to set the escalation threshold. Any user can execute sp_setsuspect_threshold to display the current settings.

Auditing

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

See also

System procedures sp_forceonline_db, sp_forceonline_page, sp_listsuspect_db, sp_listsuspect_page, sp_setsuspect_granularity