msg 824 wierd one

Question

msg 824 wierd one

Bekir-212336

SSC Rookie

Points: 29
More actions
July 20, 2009 at 4:20 pm

#207194

Apparent Power loss caused this database to go corrupt. This database in particular did not have any good backups prior to failure. Any select, or drop statements against the table produces to below error message.
select * from TBLSTHAR
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xaa955555). It occurred during a read of page (1:29364) in database ID 7 at offset 0x0000000e568000 in file 'c:\harman.mdf'. 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.
dbcc checkdb command in emergency mode or in single user mode produces following message ;
dbcc checkdb (harman,repair_allow_data_loss)
Msg 8921, Level 16, State 1, Line 1
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 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xaa955555). It occurred during a read of page (1:29364) in database ID 7 at offset 0x0000000e568000 in file 'c:\harman.mdf'. 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.
Trying to query sys.sysobjects also results in the following
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xaa955555). It occurred during a read of page (1:29364) in database ID 7 at offset 0x0000000e568000 in file 'c:\harman.mdf'. 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 need to get to the data in that particular table somehow; What other options do I have besides messing with the hex editors 🙁 or third party recovery tools ;
Any help would be really appreciated.
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

This KB Article isn't your exact problem, but does appear to be a similar one so you might want to try the suggestions.

You should also read this blog post by Paul Randal. Based on that post I would say you are out of luck without a good backup.

This is also a good post to read by Paul.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I haven't tried it, but check out the following...

http://www.softempire.com/sql-server-repair.html

... without a backup, it's gonna cost you one way or the other.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bekir-212336 SSC Rookie Points: 29 More actions · Answer 3

thanks for the replies. it was not my database or server 🙂 and as all of you predicted no backup no recovery with out going through the hex tables. They told me they used stellarsql to recover the data which cost them i think 400 dollars or so :)...no good dba means it will cost you all around :D...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

if emergency mode repair fails and you have no backup, you are out of luck. emergency mode repair is the last resort.

What might work is if you select the data from that table in small chunks, using the clustered index key to filter. You probably won't get it all out, you might get some. Emphasis on 'might'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass