Q

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.

This was first published in August 2004

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close