question

BA Posts avatar image
BA Posts asked

REPAIR_ALLOW_DATA_LOSS

Hi all, Just wanted to know whether, DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS , will show how much of data we lost, in case any is lost. Or will it ever show, that data is lost.

I have got about 5 million rows and this database is new to me.. and in case if any data is lost during the repair it would be difficult for me to analyze manually.

Can anybody suggest the best way to analyze the data lost. My count(*) on the corrupted table is not working, so even if data loss after REPAIR_ALLOW_DATA_LOSS i wont be able to analyze how much of data is lost.

Thanks, BA Posts

dbcccorruptionrepair
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLRockstar avatar image
SQLRockstar answered

Check out this post by Paul Randal

http://www.sqlskills.com/blogs/paul/post/Corruption-Last-resorts-that-people-try-first.aspx

his advice is to restore from a valid backup and not use the REPAIR_ALLOW_DATA_LOSS.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wes Brown avatar image
Wes Brown answered

If you can get the number of pages that the database has in use you can estimate the amount of loss.

did the DBCC give you a list of pages that were corrupt? You may be able to get an estimate from that as well.

If you get the list of pages you may try a dbcc page and see what is on that page. Sometimes, the page in question is a non-clustered index and may not actually effect the underlying data.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BA Posts avatar image
BA Posts answered

Thanks all for responding. Here is the results of the DBCC CHECKTABLE on the 2 tables I have issues with:

dbcc checktable('Web_claim')

Server: Msg 8928, Level 16, State 1, Line 1            
Object ID 294292108, index ID 0: Page (1:1834754) could not be processed. See other errors for details.            
Server: Msg 8944, Level 16, State 1, Line 1            
Table error: Object ID 294292108, index ID 0, page (1:1834754), row 62. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 154 and 53.            
DBCC results for 'Web_Claim'.            
There are 918513 rows in 6767 pages for object 'Web_Claim'.            
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Web_Claim' (object ID 294292108).            
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Prod.dbo.Web_Claim ).            

dbcc checktable('Claim_item')

Server: Msg 8928, Level 16, State 1, Line 1            
Object ID 1618820829, index ID 0: Page (1:1835914) could not be processed. See other errors for details.            
Server: Msg 8944, Level 16, State 1, Line 1            
Table error: Object ID 1618820829, index ID 0, page (1:1835914), row 8. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 1263 and 398.            
DBCC results for 'Claim_Item'.            
There are 5402810 rows in 314154 pages for object 'Claim_Item'.            
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Claim_Item' (object ID 1618820829).            
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Prod.dbo.Claim_Item ).            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

Backup and Restore the database to a new, temporary, database.

Repair the database (with Data Loss)

Compare the repaired database against the original (using database comparison tools such as RedGate)

If the live database is active, and changing, then after the Backup make two restores - e.g. "Original" and "Repaired" - and compare them so that the comparison is at a static state.

Then put some time into ensuring that it never happens again - e.g. doing DBCC CHECKDB regularly to ensure that there are no corruptions in the database; and restoring all backups to a.n.other server to prove that the backups files are restorable.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.