SQL Server Performance

torn page detection

Discussion in 'General DBA Questions' started by Krasavitsa, Jul 14, 2009.

  1. Krasavitsa New Member

    What is the purpose to set status in sysdatabases? My db2 is to 16 which means torn page detection (ALTER DATABASE). Can you explain more what is this mean and what is the purpose for the db to be set for this option. We are having problems with this db and I am trying to see if our configuration is correct. My db1 is set to 0, I want to know when do I need to set to a number and why is set to o
  2. Luis Martin Moderator

    <p>&nbsp;</p><dl><dt>TORN_PAGE_DETECTION { ON | OFF }</dt><dd> <dl><dt>ON</dt><dd> <p>Incomplete pages can be detected by the Database Engine.</p> </dd></dl><dl><dt>OFF</dt><dd> <p>Incomplete pages cannot be detected by the Database Engine.</p> </dd></dl> <div class="alert"><table><tbody><tr><th><!--src=[../local/note.gif]--><img src="http://i.msdn.microsoft.com/Bb522682.note%28en-us,SQL.100%29.gif" alt="Bb522682.note(en-us,SQL.100).gif">Important: </th></tr><tr><td>The syntax structure TORN_PAGE_DETECTION ON | OFF will be removed in afuture version of SQL Server. Avoid using this syntax structure in newdevelopment work, and plan to modify applications that currently usethe syntax structure. Use the PAGE_VERIFY option instead. <p><!----></p></td></tr></tbody></table></div></dd></dl>
  3. Krasavitsa New Member

    Thank you for this info, but stil why db is set 16 in status from sysdatabases and why is set to 0. status option in sysdatabases
  4. Luis Martin Moderator

    If you using Enterprise Manager, databases, yourdatabase, right click, properties in options tab, you will find severals options.
    One on then is Torn page detection. Now in BOL status is 16 with Torn page detection on.
    Also there are a lot of different status. First time I see 0 in one database.
  5. Krasavitsa New Member

    Would something like ‘Torn Pages’ be detected or handled by the optimization job?
  6. Luis Martin Moderator

    As far I know, no way.

Share This Page