Being a DBA , you will see database corruptions in your environments. I got four or five and not affected much of the data. Most of the time logical corruption and memory errors. The bellow one I got sometimes back and the worst part is backup also corrupted database.
What we can do when we got a corruption errors or tickets.
Analysis the error from the error log, if you have good entries or run CheckDB and get the messages. (Don’t go and detach the database, restart SQL or run repair_allow_data_loss)
dbcc checkdb ('DB') with no_infomsgs,all_errormsgs
I take a help of http://www.sqlservercentral.com/articles/Corruption/65804/ Gail shaw’s article to start with it.
If we have a good backup restore in another server and make sure all are good and the data are same. If you do not have a backup, Place the database in EMERGENCY mode then start troubleshooting it. (The only repair option allowed in EMERGENCY mode is REPAIR_ALLOW_DATA_LOSS.)
ALTER DATABASE [DB name] SET EMERGENCY; -- It only allow read.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:5782993) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:5782994) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data), page (1:5782992). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data): Page (1:5782992) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data): Page (1:5782993) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data): Page (1:5782994) could not be processed. See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data). The off-row data node at page (1:5782992), slot 0, text ID 912195584 is referenced by page (1:5782938), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data). The off-row data node at page (1:5782993), slot 0, text ID 912195584 is referenced by page (1:5782938), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594055688192 (type LOB data). The off-row data node at page (1:5782994), slot 0, text ID 912195584 is referenced by page (1:5782938), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8534654:4)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8534716:27)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8536618:83)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8539814:54)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8540209:23)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8540400:38)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8540693:32)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8542715:22)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8543775:32)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8543912:44)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8543946:25)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8545605:31)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data): Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8547125:51)
Msg 8929, Level 16, State 1, Line 1
Object ID 926626344, index ID 1, partition ID 72057594166575104, alloc unit ID 72057594192855040 (type In-row data):
Errors found in off-row data with ID 912195584 owned by data record identified by RID = (1:8547276:27)
CHECKDB found 0 allocation errors and 21 consistency errors in table ‘Table’ (object ID 926626344).
CHECKDB found 0 allocation errors and 23 consistency errors in database ‘DB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB ().
The last line will suggest you what can be done at minimal option. The above one said “repair_allow_data_loss is the minimum repair level” is the minimum option to get the database good condition. But it will remove the data. If it suggest repair_rebuild then it will be good.
I just restored the latest full backup another server and got the same error. I tried with repair_allow_data_loss in that restored copy (Not the original DB). It fixes the issue, but the table repaired removed 3%of rows from the count of rows.
Have tried restore the old backups one be one from tape recent to old, a month old backup was good. After a checkDB it shows good but it had 15% of data mismatch with the table. Our application is third party application. We have given another fix to search and read the data from both repaired and missing data from corrupted database. If application works by that way, we can write a data to the repaired database and read the missing data from corrupted database.
We also have to check with the storage team for any subsystem related errors and corruptions.
Bottom line: Test your backups often and run frequent checkDB for VLDBs, do not leave your VLDB if it takes more time, we have an alternative solution. https://sqlserverblogforum.com/vldb/vldb-very-large-database-dbcc-checkdb/.
