How much data will I loose? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

How much data will I loose?

Author

Topic

NewHampshire
Starting Member

14 Posts

Posted - 2009-01-08 : 13:58:52

I'm assuming that my only way of fixes this (without backups I can be sure are clean) is to run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. If I am wrong, please please please correct me. But assuming that is what I should do, how much data would I be losing here? Thanks.

__________________________________________________
DBCC results for 'Am'.
Warning: NO_INDEX option of checkdb being used. Checks on non-system indexes will be skipped.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.

Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4910418989777158144 (type Unknown), page (1:1668368). Test (m_headerVersion == HEADER_7_0) failed. Values are 25 and 1.

Msg 8939, Level 16, State 6, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4910418989777158144 (type Unknown), page (1:1668368). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.

Msg 8939, Level 16, State 7, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4910418989777158144 (type Unknown), page (1:1668368). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.

CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-08 : 14:37:32

Please can you post or attach the complete output of DBCC CHECKDB('Am') WITH No_INFOMSGS, ALL_ERRORMSGS
I'd much rather not recommend any action based on a partial output of errors. There maybe something that you left out that changes everythng.

Do you have any idea when this corruption started?

--
Gail Shaw
SQL Server MVP

NewHampshire
Starting Member

14 Posts

Posted - 2009-01-08 : 14:44:05

I'm running CHECKDB again now, and will post results.

Background on the corruption. This is my fifth day on a new job. Two weeks ago, my new empoyer had a flood in the server room. Catastrophic hardware failures have been occuring ever since. A drive that is OK one day will crap out the next, etc. The particular database in question was restored from tape 3 days ago, and it seems that the backup may have been corrupt, since the logs tell me that the corruption started about that time. So I can't rely on my backups.

This first came to my attention (I'm not in everything yet) due to a job failure. Error message from that job:

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:500980; actual 0:1159). It occurred during a read of page (1:500980) in database ID 5 at offset 0x000000f49e8000 in file 'I:\Data\Am.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. The step failed.

NewHampshire
Starting Member

14 Posts

Posted - 2009-01-08 : 14:58:40

Full DBCC CHECKDB results:

Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4910418989777158144 (type Unknown), page (1:1668368). Test (m_headerVersion == HEADER_7_0) failed. Values are 25 and 1.
Msg 8939, Level 16, State 6, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4910418989777158144 (type Unknown), page (1:1668368). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Msg 8939, Level 16, State 7, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4910418989777158144 (type Unknown), page (1:1668368). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 66099276, index ID 1, partition ID 72057594058637312, alloc unit ID 72057594131382272 (type In-row data). Page (1:1021003) is missing a reference from previous page (1:1668368). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 66099276, index ID 1, partition ID 72057594058637312, alloc unit ID 72057594131382272 (type In-row data): Page (1:1668368) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 66099276, index ID 1, partition ID 72057594058637312, alloc unit ID 72057594131382272 (type In-row data). Page (1:1668368) was not seen in the scan although its parent (1:1746944) and previous (1:1662230) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'oe_Invoice' (object ID 66099276).
CHECKDB found 0 allocation errors and 6 consistency errors in database 'Am'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Am, noindex).

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-08 : 17:16:15

quote:
This is my fifth day on a new job.

:-) Great start to a new job.

If you have no clean backup, your only option is to run CheckDB with the repair_allow_data_loss option. As it's name implies, it will cause data loss.

As far as I can see, there's one of the 'oe_Invoice' table that's damaged. CheckDB will deallocate it. Since the page is damaged, you can't read it to see what's going to be lost. There's no way to be sure of the number of rows on that page
This may help you figure out what's going to be deleted - http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Using-DBCC-PAGE-to-find-what-repair-will-delete.aspx

--
Gail Shaw
SQL Server MVP

NewHampshire
Starting Member

14 Posts

Posted - 2009-01-08 : 18:14:23

I ran CHECKDB repair allow data loss, and I appear to have only lost some nonclustered indexes which successfully rebuilt. So I guess I dodged a bullet this time. Thanks for your help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-01-08 : 20:33:04

For future reference, you may also be able to use the bcp utility to pull data out of a table, even if it's showing corruption. You can then import it into another database and/or server to examine the results in case the repair loses data.

You should also consider having another server as a standby for data repair and possible recovery, instead of running them directly on your production box. You could detach the database, copy the files to the other machine, and attach them for further work like CHECKDB and manual entry.

Paul Randal has A LOT of really good information on handling disaster recovery and preventing data loss, and since he wrote CHECKDB it's pretty solid.

You can find more info here:

http://sqlskills.com/BLOGS/PAUL/category/Corruption.aspx

And in case you go to a conference where he's speaking, I highly recommend going to his sessions.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-09 : 03:32:39

quote:
Originally posted by NewHampshire

I ran CHECKDB repair allow data loss, and I appear to have only lost some nonclustered indexes which successfully rebuilt. So I guess I dodged a bullet this time. Thanks for your help.

Are you very sure about that? The corruption errors are pointing at index 1, which is the clustered index and hence is the data.

Also, the repair_allow_data_loss would not be indicated if the corruption was in the nonclustered indexes.

It could be that the damaged page was empty, and hence you didn't lose anything.

--
Gail Shaw
SQL Server MVP

kongaung
Starting Member

8 Posts

Posted - 2009-01-09 : 05:51:45

Before you restart your computer
the one way is you can attach the mdf file and log file.
after restated all data will corrupt .

kongaung
myanmar

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-09 : 09:59:28

quote:
Originally posted by kongaung

Before you restart your computer
the one way is you can attach the mdf file and log file.
after restated all data will corrupt .

What?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-09 : 10:09:11

quote:
Originally posted by robvolk

You could detach the database, copy the files to the other machine, and attach them for further work like CHECKDB and manual entry.

I wouldn't recommend detaching a corrupt database. There's always a chance that it won't reattach. Paul's got a couple postings just that problem.

Back it up (using continue after error if necessary) and restore on another machine.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources