Answered by:
help on torn page issue

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
Answers
-
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- Proposed as answer by Tom Li - MSFTModerator Monday, June 21, 2010 8:13 AM
- Marked as answer by Tom Li - MSFTModerator Wednesday, June 23, 2010 12:57 PM
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! -
-
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
-
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.
-
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- Proposed as answer by Tom Li - MSFTModerator Monday, June 21, 2010 8:13 AM
- Marked as answer by Tom Li - MSFTModerator Wednesday, June 23, 2010 12:57 PM
-
Hi,
Go through this article, http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-bug-that-people-are-hitting-Msg-8967-Level-16-State-216.aspx