Attach DB fails - Error 824 - torn page - SQL 2005

Question

Attach DB fails - Error 824 - torn page - SQL 2005

Viewing 9 posts - 16 through 23 (of 23 total)

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

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

Andrew van Zyl (9/21/2010)
Hi Gail,
Have done exactly as suggested in the link.
Database is attached, but in suspect mode.
This is what I did subsequently, errors included:
<snip>
Any suggestions?

Errr... There are reasons I asked you to run checkDB (with just the noinfomsgs option) and do nothing else. Still, no going back...

Does alter database set emergency work? If not, post the exact and complete error.

What, if any, entries are there in the SQL error log for this database? Post them all.

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

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 2

My apologies...put it down to blind terror 🙂

Set emergency mode ran ok. It was set to single user too and when I ran set to multi_user, this ran ok too.

Just cannot access tables etc

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

Please put it into emergency mode and leave it there. Then please look in the SQL error log for the messages I've asked for.

In my experience in cases like this, acting in panic results in further damage, not fixes.

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

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 4

Gail

DBCC CheckDB (HeatCS) WITH No_INFOMSGS, ALL_ERRORMSGS gives me Database 'HeatCS' is being recovered. Waiting until recovery is finished.

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

Yup, guessed that from earlier posts. Still want to see all relevant error log entries.

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

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 6

Sorry, my mistake. I was led to believe it was a SQL 2008 DB, not 2005.

I'm now working in 2005. I created the dummy DB (same file names and sizes), stopped the service, replaced the .mdf and .ldf files, then re-started the service.

I then set the DB to emergency mode. This failed, message below:

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xaaaaaaa9). It occurred during a read of page (1:16) in database ID 9 at offset 0x00000000020000 in file 'D:\SQL DATA\Recover\HeatCS.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)

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

You are not going to like this.

The page that's damaged (page 16) is the first page in the table sysallocunits. Sysallocunits is the table that contains info on where every other table in the database (including the system tables) are stored.

There is absolutely no way this DB is going to be queryable, ever. SQL has no idea where its system tables are, let alone what user objects exist in the DB.

You can give those data recovery tools a try, I make no guarantees, but there is no way to repair this in SQL.

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

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 8

Thanks Gail, I really appreciate your assistance.

Can you suggest a SQL Recovery tool to try?

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

Andrew van Zyl (9/21/2010)
Can you suggest a SQL Recovery tool to try?

No, I've never personally used one. One (I don' know name) that a colleague once tried didn't work at all, so don't believe the advertising, don't buy unless you're absolutely sure it works.

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