Problem solve Get help with specific problems with your technologies, process and projects.

Knowing what data will be lost with repair_allow_data_loss option

My client is using SQL Server 7 as the databse. When he ran DBCC checktable on 'FOutStanding' table, following errors were reported:
Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1289107683, index ID 0:
Page (1:8244) could not be processed. 
See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1 Table Corrupt: Object ID
1289107683, index ID 0, page (1:8244), row 11. 
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 148 and 2.
DBCC results for 'FOutStanding'.
There are 9242 rows in 187 pages for object 'FOutStanding'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in
table 'FOutStanding' (object ID 1289107683).
repair_allow_data_loss is the minimum repair level for the errors found by 
DBCC CHECKTABLE (FMComp005.dbo.FOutStanding ).
Now, if I ask my client to run DBCC CheckTable with repair_allow_data_loss option, I know, some data will be lost. Is there any way to know beforehand, what data will be lost?

In order to determine the rows having consistency or allocation issues on a specific table, DBCC CHECKTABLE commands...

should be issued and will indicate the pages with the issues. Rather then issuing the DBCC CHECKTABLE with the REPAIR_ALLOW_DATA_LOSS, it may be more advantageous to following these steps:

  • Issue DBCC CHECKDB to determine all errors in a specific database
  • Issue DBCC CHECKCATALOG to determine any schema related issues in a specific database
  • Issue DBCC CHECKTABLE to determine the errors on the specific table
  • Create another table with the same DDL as the tables with an issue and add a suffix of '_New'
  • Issue SELECT statements with a WHERE clause utilizing a range against the original table to determine which can be read
    • It may be necessary to fine to this query to a single row and then to particular columns to determine which data is not retrievable
  • Insert the data via a SELECT statement into the '_New' table
  • Issue DBCC CHECKTABLE to verify that the new table is error free
  • Attempt to salvage the remainder of the data from query a previous backup
  • Backup the database
  • Rename the original table to have a suffix of '_Old'
  • Rename the '_New' table to have no suffix
  • Drop the '_Old' table
  • Test the application to ensure it performs as expected
  • Issue DBCC CHECKDB to ensure no allocation or consistency issues in the entire database

If this does not solve the problem, then issue DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, but expect to lose the data originally indicated in the DBCC CHECKDB statement plus the potential for any data with a linkage pointer.

Next Steps

Discover the benefits of DBCC CHECKDB for Analysis Services 2016


This was last published in August 2004

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.