The suspect escalation threshold is the number of suspect pages at which recovery marks an entire database suspect, even if the recovery isolation mode is “page.” By default, it is set to 20 pages in a single database. You can use sp_setsuspect_threshold to change the suspect escalation threshold.
The syntax for sp_setsuspect_threshold is:
sp_setsuspect_threshold [dbname [,threshold]]
With the dbname and threshold arguments, sp_setsuspect_threshold displays the current and configured suspect escalation threshold settings for the specified database. Without any arguments, it displays these settings for the current database.
You configure recovery fault isolation and the suspect escalation threshold at the database level.
You cannot execute sp_setsuspect_granularity or sp_setsuspect_threshold inside a transaction.
You must have the sa_role and be in the master database to set values with sp_setsuspect_granularity and sp_setsuspect_threshold. Any user can execute these procedures with only the name of the database as an argument to display the values configured for that database, as illustrated below:
sp_setsuspect_granularity pubs2
DB Name Cur. Suspect Gran. Cfg. Suspect Gran. Online mode ------- ------------------ ------------------ ----------- pubs2 page page read/write
sp_setsuspect_threshold pubs2
DB Name Cur. Suspect threshold Cfg. Suspect threshold ------------- ------------------------ ---------------------- pubs2 20 30
This example shows that the recovery isolation mode for the pubs2 database was “page” and the escalation threshold was 20 the last time recovery ran on this database (the current suspect threshold values). The next time recovery runs on this database, the recovery isolation mode will be “page” and the escalation threshold will be 30 (the configured values).
With no arguments, sp_setsuspect_granularity and sp_setsuspect_threshold display the current and configured settings for the current database, if it is a user database.
