repair_allow_data_loss on prod

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

SQL Server 2005 Forums

SQL Server Administration (2005)

repair_allow_data_loss on prod

Author

Topic

DBA007
Posting Yak Master

145 Posts

Posted - 2010-07-28 : 04:57:05

The DBIntegrity on server 'P1' for database 'one' was failing because of the below error,can any one shed some light on this,we cant run repair_allow_data_loss on this as it is a prodserver.

Msg 8928, Level 16, State 1, Line 1
Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data): Page (1:175852) could not be processed. See other errors for details.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data). Page (1:175853) is missing a reference from previous page (1:175852). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data). Page (1:175852) was not seen in the scan although its parent (1:175747) and previous (1:175851) refer to it. Check any previous errors.
Msg 8944, Level 16, State 24, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data), page (1:175852), row 40. Test (ColumnOffsets + (int)sizeof (UINT16) <= (nextRec - pRec)) failed. Values are 174 and 172.
Msg 8944, Level 16, State 24, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data), page (1:175852), row 40. Test (ColumnOffsets + (int)sizeof (UINT16) <= (nextRec - pRec)) failed. Values are 174 and 172.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'tblone' (object ID 873822225).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'one'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (one).

Kristen
Test

22859 Posts

Posted - 2010-07-28 : 05:12:08

If FULL recovery model then take a Tail backup of the Log, then restore Full, Tlogs and Tail to a new temporary database and run CHECKDB. If that's clean then repeat on Production database.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-28 : 05:54:44

Your options are basically limited to restore from a clean backup (as Kristen detailed) or run checkDB with repair-allow_data_loss and lost one page of data in the table tblone.

Do you have a full database backup from before this corruption occurred? Do you have an unbroken chain of log backups from that full backup up until now?

--
Gail Shaw
SQL Server MVP

DBA007
Posting Yak Master

145 Posts

Posted - 2010-07-29 : 14:45:12

i took the prodbackup to test server and ran repair_allow_data_loss on it but how can i know that there is loss of data

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-30 : 04:49:43

There will be. The repair level is repair_allow_data_loss and you have one page of the clustered index in table tblone damaged. Hence checkDB is going to discard one page of data. It'll say that in the repair output.

How to tell what was lost? Well that you can't unless you have a backup from before the corruption occurred (and if you did, you wouldn't need to repair)

--
Gail Shaw
SQL Server MVP

DBA007
Posting Yak Master

145 Posts

Posted - 2010-08-02 : 11:34:25

Gail,this was the result i got after running it on test server,i dont find loss of data over the below statements,can you help me over this.

DBCC results for 'tblone'.
Repair: The Clustered index successfully rebuilt for the object "dbo.tblone" in database "one".
Repair: The page (1:175852) has been deallocated from object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data).
Repair: The Nonclustered index successfully rebuilt for the object "dbo.tblone, IDX_TC_ID" in database "one".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.tblone, IDX_STATUS_ID" in database "one".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.tblone, IX_tblone_ProjectTaskID" in database "one".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.tblone, idx_ToVector" in database "one".
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 2
Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data): Page (1:175852) could not be processed. See other errors for details.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data). Page (1:175853) is missing a reference from previous page (1:175852). Possible chain linkage problem.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data). Page (1:175852) was not seen in the scan although its parent (1:175747) and previous (1:175851) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 24, Line 2
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data), page (1:175852), row 40. Test (ColumnOffsets + (int)sizeof (UINT16) <= (nextRec - pRec)) failed. Values are 174 and 172.
The error has been repaired.
Msg 8944, Level 16, State 24, Line 2
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data), page (1:175852), row 40. Test (ColumnOffsets + (int)sizeof (UINT16) <= (nextRec - pRec)) failed. Values are 174 and 172.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 6 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 7 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 8 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 873822225, index ID 9 will be rebuilt.
The error has been repaired.
There are 5052796 rows in 142295 pages for object "tblone".

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-02 : 12:08:21

One page of data discarded:
Repair: The page (1:175852) has been deallocated from object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data).

Index 1 is the clustered index, that is the actual data pages of the table.

--
Gail Shaw
SQL Server MVP

DBA007
Posting Yak Master

145 Posts

Posted - 2010-08-02 : 14:06:53

can you guide me how to solve this,with out loosing data on that page or will the data be moved prior to page being dropped

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-02 : 16:24:48

Restore from a clean backup.

The page is corrupt, ie not readable. Anything on that page is potentially garbage. CheckDB with repair_allow_data_loss will discard that page, with anything on it, then fix up the links so that everything is working again. Anything on that page is gone. That is why you should always have backups and that is why restoring from a clean backup is the recommended way of fixing corruption.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources