none
help on torn page issue RRS feed

  • Question

  • 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

  •  

    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
  • 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
  • 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
  • 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
  • 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