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


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.

Please create a username to comment.