none
Torn Page Detection - Advice RRS feed

  • Question

  • If I run:

    SELECT   log_reuse_wait_desc, page_verify_option_desc, *
    FROM  sys.databases
    


    it shows TORN PAGE DETECTION across a few of my databases.

    What does this mean and is it of any significance, i.e. do I need to action anything?


    You can't dangle the bogus carrot of possible reconciliation in front of me whilst riding some other donkey
    Tuesday, January 5, 2010 2:50 PM

Answers

  • As promised, here is how you can detect torn pages and fix them

    --Find the torn page. If no results returned then you are fine
    SELECT db_name(database_id) DatabaseName, file_id, page_id, last_update_date
    FROM msdb..suspect_pages
    WHERE event_type = 3
    
    --Backup your transaction log
    USE master
    BACKUP LOG DBName
    TO DISK = 'C:\DBName.trn'
    WITH NORECOVERY
    
    --Restore Torn Page. 1 is the file_id, and 123 is the page_id from the first query
    RESTORE DATABASE DBName PAGE='1:123'
    FROM DISK='C:\DBName.bak'
    WITH NORECOVERY
    
    
    --Restore your log
    RESTORE LOG DBName FROM
    DISK='C:\DBName.trn'
    WITH RECOVERY


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Zongqing Li Monday, January 11, 2010 9:06 AM
    Thursday, January 7, 2010 5:08 AM

All replies

  • Torn pages happen when some of the pages (the 8K) in SQL Server are not fully written to the memory, propably due to hardware failure.
    Do you have any recent backup to restore from?

    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 5, 2010 2:59 PM
  • Of the databases showing the TPD, they have initially come from SQL 2005 servers and are now sitting on brand new 64 bit servers with SQL 2008 installed.
    We don't see any problems with the databases themselves. Is there something that can be run that can just fix the errors?

    We take full backups once a week and then differentials throughout the rest of the week.
    You can't dangle the bogus carrot of possible reconciliation in front of me whilst riding some other donkey
    Tuesday, January 5, 2010 3:05 PM
  • You may try DBCC CHECKDB command

    Thanks,

    Sandeep

     

    Tuesday, January 5, 2010 3:18 PM
  • There is a way to find and fix the torn pages. I'll try to find the script and send it to you.

    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 5, 2010 3:23 PM
  • You can change the option from database properties.
    Use below query to find the database name is having torn page detection

     

    SELECT log_reuse_wait_desc, page_verify_option_desc, *

    FROM sys.databases

    where page_verify_option_desc like 'torn%'


    then go to the properties of database
    à go to optionà go to recovery --page verify option à select checksum.


    Then you will not find this torn page detection


    Thanks,

    Sandeep


    Tuesday, January 5, 2010 3:30 PM
  • By the way, this option in sys.databases(page_verify_option_desc) shows you that TORN_PAGE_DETECTION is ON but not necessarily that you have torn pages. This means that the database will detect the torn pages when there is any.
    I will give you the script to find torn pages once I have access to it.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 5, 2010 3:50 PM
  • Yes, you are correct abdshall


    Thanks,

    Sandeep



    Tuesday, January 5, 2010 3:58 PM
  • As promised, here is how you can detect torn pages and fix them

    --Find the torn page. If no results returned then you are fine
    SELECT db_name(database_id) DatabaseName, file_id, page_id, last_update_date
    FROM msdb..suspect_pages
    WHERE event_type = 3
    
    --Backup your transaction log
    USE master
    BACKUP LOG DBName
    TO DISK = 'C:\DBName.trn'
    WITH NORECOVERY
    
    --Restore Torn Page. 1 is the file_id, and 123 is the page_id from the first query
    RESTORE DATABASE DBName PAGE='1:123'
    FROM DISK='C:\DBName.bak'
    WITH NORECOVERY
    
    
    --Restore your log
    RESTORE LOG DBName FROM
    DISK='C:\DBName.trn'
    WITH RECOVERY


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Zongqing Li Monday, January 11, 2010 9:06 AM
    Thursday, January 7, 2010 5:08 AM