To isolate the suspect pages so that only they are taken offline, while the rest of the database remains accessible to users, use the sp_setsuspect_granularity to set the recovery isolation mode to “page.” This mode is in effect the next time that recovery is performed in the database.
The syntax for sp_setsuspect_granularity is:
sp_setsuspect_granularity
[dbname [,{"database" | "page"} [, "read_only"]]]
With the dbname and either database or page as the second argument, sp_setsuspect_granularity sets the recovery isolation mode.
Without the database or page argument, sp_setsuspect_granularity displays the current and configured recovery isolation mode settings for the specified database. Without any arguments, it displays those settings for the current database.
If corruption cannot be isolated to a specific page, recovery marks the entire database as suspect, even if you set the recovery isolation mode to “page.” For example, a corrupt transaction log or the unavailability of a global resource causes this to occur.
When recovery marks specific pages as suspect, the default behavior is for the database to be accessible for reading and writing with the suspect pages offline and therefore inaccessible. However, if you specify the read_only option to sp_setsuspect_granularity, and recovery marks any pages as suspect, the entire database comes online in read_only mode and cannot be modified. If you prefer the read_only option, but in certain cases you are comfortable allowing users to modify the non-suspect pages, you can make the online portion of the database writable with sp_dboption:
sp_dboption pubs2, "read only", false
In this case, the suspect pages remain offline until you repair them or force them, as described in “Bringing offline pages online”.
