Post reply

torn page was detected and marked suspect

  • November 27, 2007 at 8:58 pm

    #377194

    Hi Experts,

    I have this 100GB DB and was marked suspect. Below are the errors I was able to capture:

    Error: 823, Severity: 24, State: 1

    I/O error (torn page) detected during read of BUF pointer = 0x14be7940, page ptr = 0x43d8a000, pageid = (0x15:0x1041e), dbid = 8, status = 0x801, file = F:\DATABASE\ISHNau_Data_18.NDF.

    Error: 3414, Severity: 21, State: 1

    Database 'ISHNau' (database ID 8) could not recover. Contact Technical Support.

    How can i online this database without restoring from backup?

    Thanks,

    Art

  • November 27, 2007 at 10:22 pm

    #754825

    Hi

    I got this info from BOL

    Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.

    Also please checkout this KB article

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339

    Hop this helps.

    Ameena

  • November 27, 2007 at 10:23 pm

    #754827

    Bummer, a quick lookup of the error message on msdn provides the following info, and document

    I/O logical check failure: If a read Windows API call or a write Windows API call for a database file is successful, but specific logical checks on the data are not successful (a torn page, for example), an 823 error is raised. The following error message is an example of an 823 error for an I/O logical check failure:

    2003-09-05 16:51:18.90 spid17 Error: 823, Severity: 24,

    State: 2

    2003-09-05 16:51:18.90 spid17 I/O error (torn page) detected during read at offset 0x00000094004000 in file 'F:\SQLData\mydb.MDF'..

    To resolve this problem, first run the DBCC CHECKDB statement on the database that is associated with the file in the error message. If the DBCC CHECKDB statement reports errors, correct those errors before you troubleshoot this problem. If the problem persists even after the DBCC CHECKDB errors have been corrected, or if the DBCC CHECKDB statement does not report any errors, review the Microsoft Windows NT system event log for any system errors or disk-related errors. You can also contact your hardware vendor to run any appropriate diagnostics.

    here is the Link

    http://support.microsoft.com/kb/828339

    :crying:

  • November 28, 2007 at 11:42 am

    #755070

    Looks like you've hit this page corruption during a transaction rollback or crash recovery. There's no good way out of this on 2000 except to restore from a backup. Your other options are:

    1) go into emergency mode and extract as much data as possible into a new database

    2) go into emergency mode, rebuild the log (which will cause transactional and structural inconsistencies in your database), then bring the database online and run a CHECKDB with REPAIR_ALLOW_DATA_LOSS to fix all the problems created by rebuilding the log. I don't recommend this option at all. See this blog post for details of why not: http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx

    Whichever you decide to do, afterwards implement a sound backup strategy. And don't detach the database - it'll just fail to reattach when recovery fails again.

    Let me know how you get on.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • November 28, 2007 at 7:33 pm

    #755210

    Hi Paul,

    How can i go into emergency mode?

    Thanks

  • November 28, 2007 at 7:56 pm

    #755217

    Hi Art,

    Check the following link http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-suspect-database/[/url]

    You will find how to get into emergency mode

    Regards,

    Ahmed

  • November 30, 2007 at 3:28 am

    #755856

    To bring the database to emergency mode

    Exec sp_configure 'allow updates',1 reconfigure with override

    update sysdatabases set status = 32768 where name ='dbname'

    Please check BOL for more info.

    Rememeber 823 error generally a hardware issue so I recommend you to push the data to the new machine. Check your hardware,firmware etc of this old system. you will find SQLIOStress tool in the microsoft site that is the best tool I recommend you to check for the I/O related issues.

    "More Green More Oxygen !! Plant a tree today"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply