SQL Server Performance

How to handle error 824?

Discussion in 'SQL Server 2005 General DBA Questions' started by pcsql, Jan 8, 2008.

  1. pcsql New Member

    A client has encountered the error 824 and the sql server log contains this information:
    SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55565555). It occurred during a read of page (3:757128) in database ID 5 at offset 0x00000172150000 in file 'c:mydata...
    Running dbcc checkdb on that database returns no error.
    What are the possible causes for torn page?
    Should I ask my client to run some MS utilities? I find a MS utility named SQLIOSim.
    Thanks for any help.
  2. Luis Martin Moderator

    Check:
    http://technet.microsoft.com/en-us/library/ms174425.aspx
    HTH
  3. pcsql New Member

    Hi Luis,
    I have also read that page but I don't know how I can use the data from the suspect_pages table to resolve the error or pinpoint which database object is corrupted.
    Thanks.
  4. satya Moderator

    What level of service pack on SQL you have here?
    Also I would suggest this is a hardware based problem that is causing torn page and error on the data file, could be a controller issue or mismatch of firmware drivers.
    Check Event logs for Hardware problems. Database corruption is usually caused by failing hardware, and obviously a good idea to fix the hardware before proceeding. Maybe a disk in a RAID failed and the controller failed to complete the disk write (that would cause a Torn page error); a power cut could probably do it too.
    Additionally look at the TEMPDB contention too. FOr data correction you could create another table and use DTS to import rows from this troubled table, as RESTORE will not have resolution as it will restore the corrupted page again.
  5. pcsql New Member

    Hi Satya,
    I believe the server pack is either SP1 or SP2 of SQL Server 2005. The SQL Server 2005 is installed on a virtual server.
    I also speculate that it is hardware problem. Should DBCC CHECKDB report torn page corruption? Is torn page a physical corruption or a logical (not sure how to term this) corruption happened during I/O? The error for my client occurred during high I/O.
    If it is hardware problem, should MS tool like SQLIOsim.exe report errors?
  6. satya Moderator

    [:)] Too many questions...
    Check what kind of SP you have, do not speculate and it is better to have a thorough checkout on the physical server from the vendor.
    DBCC CHECKDB is required for database consistency checkup, high IO means you need to see what kind of queries are running. SQLIOSIM is for simulating activity for stress testing and not for hardware checking.
  7. pcsql New Member

    Hi Satya,
    I know it is alot of questions since I'm kind of confused about torn page and why DBCC CHECKDB will not report it.[:^)]
    If a database has the Page Verify option set to checksum or torn page detection, I always think DBCC CHECKDB (without using any argument) will able to report torn page on the current database. My understanding of torn page is a physical corruption.
    The query caused the error is altering a table by adding a column.
    Regarding SQLIOSim, I'm thinking that by doing the stress testing, certain warnings and/or errors (such as long I/O response) reported can be an indication of potential hardware problem.
    SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55565555). It occurred during a read of page (3:757128) in database ID 5 at offset 0x00000172150000 in file 'c:mydata..
    Based on the above error, I believe the database has the Page Verify option set to torn page detection since it mentions signature difference. I don't know how to interpret page (3:757128) and offset 0x00000172150000.
  8. satya Moderator

    Torn page happens due to disk I/O issues as per KBA http://support.microsoft.com/kb/828339 and what you got from DBCC CHECKDB is related to the same and we cannot say this should report it. And also it depens upon the PAGE_VERIFY option.

Share This Page