Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: