Help with Data Corruption

SQL Server Administration
1

Hi all,

I am attempting to assist a customer who has had some database corruption and doesn't have a good backup history.

As far as I can tell from the following CHECKDB result, there is no easy repair to be had because the corruption is in index 1 (= actual table data).

I just wanted to confirm that there were no easy options to resolve this type of corruption. At the moment I've advised that it doesn't look good and he needs to start considering reconstituting the data from alternate sources and the last known good backup.

Thanks in advance

S

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:17812823) with latch type SH. 1(error not found) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:18309934) with latch type SH. 1(error not found) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:18449461) with latch type SH. 1(error not found) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:20123965) with latch type SH. 1(error not found) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:20123967) with latch type SH. 1(error not found) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:20134589) with latch type SH. 1(error not found) failed.
CHECKDB found 0 allocation errors and 6 consistency errors not associated with any single object.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:15879728) is missing a reference from previous page (1:20123965). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:17812823) allocated to object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:17812823) was not seen in the scan although its parent (1:19146206) and previous (1:17715204) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:17893052) is missing a reference from previous page (1:17812823). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:18309934) allocated to object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:18309934) was not seen in the scan although its parent (1:16289065) and previous (1:18242352) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:18424818) is missing a reference from previous page (1:18309934). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:18840165) is missing a reference from previous page (1:20123967). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20123965) allocated to object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:20123965) was not seen in the scan although its parent (1:20151970) and previous (1:20151971) refer to it. Check any previous errors.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20123967) allocated to object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:20123967) was not seen in the scan although its parent (1:18838767) and previous (1:19975745) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:20130895) is missing a reference from previous page (1:20134589). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20134589) allocated to object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 1, partition ID 404068565188608, alloc unit ID 404068565188608 (type In-row data). Page (1:20134589) was not seen in the scan although its parent (1:14532975) and previous (1:20053803) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data). Page (1:17144901) is missing a reference from previous page (1:19842295). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:18449461) allocated to object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data). Page (1:18449461) was not seen in the scan although its parent (1:15416544) and previous (1:18204837) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data). Page (1:18674256) is missing a reference from previous page (1:18449461). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data): Page (1:19842295) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data), page (1:19842295). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 79824905 and -1.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1870629707, index ID 3, partition ID 967018518609920, alloc unit ID 967018518609920 (type In-row data). Page (1:19842295) was not seen in the scan although its parent (1:16339708) and previous (1:19055177) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 22 consistency errors in table 'Transactions' (object ID 1870629707).
CHECKDB found 0 allocation errors and 28 consistency errors in database 'Configuration'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CardData).

2

Might be worth trying to export data for each table into a new, clean, database. I have found, in the past, that by using ranges on columns values, related to various indexes on a table, that it is often possible to export most of a broken table. Can also check the RowCount for each table (i.e. the value held in the meta data, rather than using a COUNT(*) which may fail if clustered index is corrupted) and comparing that against the actual number of rows Exported for each table - e.g. if all rows appeared to export OK but the COUNT Meta Data is different to the actual COUNT(*) on the clean database.

You might find that that is no better than just using the REPAIR option of CHECKDB, but at least using an export approach you "know" that you failed to export "Between X and Y", whereas with CHECKDB I've never found a way to know what was thrown away during a Repair operation.

Fix the backups too ... and the client's procedure to regularly test that the backups are working!

3

Hi,

The best way is restoring form backup. There is no free tool which can repair corrupt SQL database. Some paid tools are available for SQL database recovery. You can easily search them on your favorite search engine.

4

To repair corrupt SQL Database

• First stop SQL Server instance
• Copy MDF and LDF files to another location
• Delete original MDF and LDF files
• Start SQL Server instance again
• Create a new database with the exact same name and file names
• Stop SQL Server
• Overwrite newly created MDF and LDF.

After this your database should be back online.

If this won’t work for you then your only option is to use a third party tool like SQL Data Recovery Tool