none
Torn Page in sql server RRS feed

  • Question

  • I detect torn_page_detection in sys.databases

    so I ran below query

    SELECT

     * FROM msdb..suspect_pages

    but no row found

    • Edited by J P IT Friday, June 7, 2013 10:06 PM
    Friday, June 7, 2013 10:05 PM

Answers

  • you dont need to worry it just shows TORN_PAGE_DETECTION is enabled for pages of that db

      if dbcc checkdb wud have given any error then it wud be a concern..not this

    Torn Page Detection writes a bit for every 512 bytes in the page.  This allows you to detect when a page was not successfully written to disk, but does not tell you if the data stored in those 512 bytes is actually correct as a couple of bytes may have been written incorrectly.
    for more details see below link

    http://jongurgul.com/blog/torn_page_detection-internals-sql-2005-onwards/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Thursday, June 13, 2013 4:15 PM
    • Marked as answer by J P IT Thursday, June 13, 2013 7:21 PM
    Thursday, June 13, 2013 4:11 PM

All replies

  • If you come across the error:

    "I/O error (torn page) detected 
    it is because SQL Server tried to access a page that had previously not been written to disk correctly. This can happen if there's a power failure or a disk or other hardware failure when the disk is being written to.
    When SQL Server detected the torn page, it probably ended your connection. If the torn page was detected during database recovery, then SQL Server will mark it as SUSPECT.

    Torn page detection is set as part of the database recovery options and enables SQL Server to detect incomplete I/O operations.

    How to Fix Torn Pages
    Run DBCC checkdb see for inconsistencies
    Check your error logs first and then restore your last backups and transaction logs. This should fix the problem, but you should also check your disks and fix any issues if there was a disk hardware failure.

    If the error is limited to index pages, you may be able to rebuild the index.

    How to Prevent Torn Pages

    The best way to prevent torn pages is to use battery-backed disk controllers. All data will be successfully written to disk, or not written at all. There are some issues with using battery-backed controllers,

    More details

    http://www.sqlserverclub.com/articles/sql-server-msg-824-torn-page-detected.aspx

    PS: if u r not getting any row i dont think ,,dere is corrution..what made u think of corruption did u chk sql server errorlog


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Saturday, June 8, 2013 9:05 AM
    • Marked as answer by Fanny LiuModerator Thursday, June 13, 2013 9:48 AM
    • Unmarked as answer by J P IT Thursday, June 13, 2013 1:28 PM
    Saturday, June 8, 2013 5:25 AM
  • Hi JP

    msdb..suspect_pages will keep detail of all pages found as corrupted , but for that you have to run DBCC check db first.

    To recover database from corrupted pages refer : Suspect Pages in database


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Sunday, June 9, 2013 5:43 PM
  • I ran DBCC check first then after I look msdb..suspect_pages but result

    So I am looking for any hardware problem

    Thursday, June 13, 2013 1:34 PM
  • I ran DBCC check first then after I look msdb..suspect_pages but result

    So I am looking for any hardware problem

    can u please elaborate what ur trying to say..DBCC checkdb didnt gave u any errors..den u moved to msdb.._suspect_pages den also no error...so what are u actually loking for..do u feel u have hardware prob ?


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 13, 2013 2:41 PM
  • Hi,

    Thanks for you reply.

    I ran below command.

    select

    name,page_verify_option_desc from sys.databases

    where

     page_verify_option_desc like 'torn%'

    and gave me below result

    database                           page_verify_option_desc

    xxxxx                                  TORN_PAGE_DETECTION

    so I ran DBCC CheckDB

    list of tables with row with out error

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxxxx'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Thursday, June 13, 2013 4:03 PM
  • you dont need to worry it just shows TORN_PAGE_DETECTION is enabled for pages of that db

      if dbcc checkdb wud have given any error then it wud be a concern..not this

    Torn Page Detection writes a bit for every 512 bytes in the page.  This allows you to detect when a page was not successfully written to disk, but does not tell you if the data stored in those 512 bytes is actually correct as a couple of bytes may have been written incorrectly.
    for more details see below link

    http://jongurgul.com/blog/torn_page_detection-internals-sql-2005-onwards/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Thursday, June 13, 2013 4:15 PM
    • Marked as answer by J P IT Thursday, June 13, 2013 7:21 PM
    Thursday, June 13, 2013 4:11 PM