none
help on torn page issue RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I am new to DBA job, and currently we have an issue for a database. When I do DBCC CHECKDB, I got below error message:

    DBCC CHECKDB ('TEST') WITH ALL_ERRORMSGS, NO_INFOMSGS

    and got below error message:

    Msg 8921, Level 16, State 1, Line 2

    Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

    Msg 824, Level 24, State 2, Line 2

    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x41d8cc75; actual: 0xaf466256). It occurred during a read of page (1:49225) in database ID 31 at offset 0x00000018092000 in file 'e:\TEST.mdf:MSSQL_DBCC31'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    I checked SQL Server error log and got the same error message; from event viewer for application I got below error (almost the same):

    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x41d8cc75; actual: 0xaf466256). It occurred during a read of page (1:49225) in database ID 30 at offset 0x00000018092000 in file 'e:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\TEST.mdf:MSSQL_DBCC30'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    Since I got nothing useful, I used last backup (there is no error message for backup error from SQL Server log or event viewer) to restore to a new database, and restoration is successful. But I then used command

    DBCC CHECKDB ('TEST') WITH ALL_ERRORMSGS, NO_INFOMSGS

    and got below error:

    Msg 8921, Level 16, State 1, Line 2

    Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

    Msg 824, Level 24, State 2, Line 2

    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x41d8cc75; actual: 0xaf466256). It occurred during a read of page (1:49225) in database ID 31 at offset 0x00000018092000 in file 'e:\TEST.mdf:MSSQL_DBCC31'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


    I then run
    DBCC CHECKDB (TEST, REPAIR_ALLOW_DATA_LOSS) and got the same error message.

    I also did

    DBCC PAGE ('TEST',1, 49225, 3)

     I got

    Metadata: ObjectId = 60  

    Blob row at: Page (1:49225) Slot 0 Length: 8054 Type: 3 (DATA)

    Blob Id:438960128

     I tried again to do repair, but still got the same error.

    I tried to find object ID 60 in the database, but error message showed me no this object.

    What should I do next?

    Thanks,

    Megan

    Wednesday, June 16, 2010 5:07 PM
    |

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

     

    Your current database is cprrupt state and the database backup also contains the corrupted state of the database.:(


    It is unfortunate that you may need to create a new database and Copy all objects/data to new one then rename it to old one.  

    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Thursday, June 17, 2010 1:35 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    How can I tell it's corrupt because of cannot be repaired or because of cannot find the object ID from sp_helpdb? How can I tell it's system database corrupt?

    Thanks.

    Thursday, June 17, 2010 2:14 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Is corrupted because the DBCC reported as corrupted, even if you are able to run the Repair Allow Data loss , the final state will be a database consistent from the point of view of the database structures but the data could be miss or corrupted (like a text column that instead of have name have weird characteres)

    I'm wondering how are you trying to get the object id using sp_helpdb. Could you try this

    use test

    select object_name(60)

    or

    select * from sysobjects where id=60

    Thursday, June 17, 2010 3:33 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry, my mistake!

    I used sp_helpdb to find database ID. For the object, I used select object_name(60), and it's not a true table since it shows me not exist.

    I mentioned sp_helpdb is because when I looked at SQL Server error log or event viewer, I got database ID like 30, but from sp_helpdb for this database I got database ID as 29. I am confused and wondering if something wrong with system tables.

    Thursday, June 17, 2010 4:32 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    There's corruption in the database and the system tables are affected. Since checkDB can't check the DB, there's no it's going to be repairable. If you don't have a clean backup, the only option left is to script all the objects out, export all data (some may well fail)  and recreate the database.

    Take a look at this article. http://www.sqlservercentral.com/articles/65804/

    Gail Shaw
    Saturday, June 19, 2010 7:14 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

     

    Go through this article, http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-bug-that-people-are-hitting-Msg-8967-Level-16-State-216.aspx

     

    Monday, June 21, 2010 4:33 AM
    |