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
This was first published in August 2004