To isolate the suspect pages so that only they are taken offline, while the rest of the database remains accessible to users, use 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. See Reference Manual: Procedures.
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 the recovery isolation mode is set 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 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”.
