torn page question

According to BOL 2000:

If a torn page is detected, an I/O error is raised and the connection is
killed. If the torn page is detected during recovery, the database is also
marked suspect. The database backup should be restored, and any transaction
log backups applied, because it is physically inconsistent.

By default, TORN_PAGE_DETECTION is ON.

The current setting of this option can be determined by examining the
 IsTornPageDetectionE
nabled property of DATABASEPROPERTYEX.



I dont want my DB to automatically go into a Suspect state. Will this only
occur if Torn Page Detection is enabled, or just in general.



TIA, ChrisR

Hi

Not only then. If during DB startup, an inconsistencies are detected in the
GAM or other system structures in the DB, it will be marked suspect. If a
file that belongs to the DB can not be opened, it will be marked as suspect.

Put it this way, if SQL Server finds a problem, it is a very bad idea to
allow further work to be done in the DB. You might end up loosing it all.

At the first hint of corruption, the DB should be taken offline and you
should investigate the problem.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"ChrisR" <noemail@bla.com> wrote in message
news:%23dtU6zZTFHA.2908@TK2MSFTNGP10.phx.gbl...
> According to BOL 2000:
>
> If a torn page is detected, an I/O error is raised and the connection is
> killed. If the torn page is detected during recovery, the database is also
> marked suspect. The database backup should be restored, and any
> transaction log backups applied, because it is physically inconsistent.
>
> By default, TORN_PAGE_DETECTION is ON.
>
> The current setting of this option can be determined by examining the
>  IsTornPageDetectionE
nabled property of DATABASEPROPERTYEX.
>
>
>
> I dont want my DB to automatically go into a Suspect state. Will this only
> occur if Torn Page Detection is enabled, or just in general.
>
>
>
> TIA, ChrisR
>
>