How did a database restore fix my consitency errors?
After a power outage, I found consistency errors in my database. A failing report is what alerted us. Below are excerpts from the DBCC CHECKDB. The first thing I did was take a full backup and restore it to a dev server so that I could test it further. When I restored the full and ran checkdb, it came out clean, no errors. After double checking the results, I did the same to the production database. I stopped all transactions, took a full backup and immediately restored that full backup. When I ran checkdb again against production, it came back clean and the original issue (the report) that alerted us to the problem was resolved as well. My question is, how did the restore fix the consistency errors? ... DBCC results for 'ACCOUNTING_AUDIT_TRAIL'. Msg 8928, Level 16, State 1, Line 1 Object ID 117575457, index ID 1, partition ID 72057595151450112, alloc unit ID 72057595206762496 (type In-row data): Page (1:250256) could not be processed. See other errors for details. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 117575457, index ID 1, partition ID 72057595151450112, alloc unit ID 72057595206762496 (type In-row data). Page (1:250256) was not seen in the scan although its parent (1:338534) and previous (1:243104) refer to it. Check any previous errors. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 117575457, index ID 1, partition ID 72057595151450112, alloc unit ID 72057595206762496 (type In-row data). Page (1:254248) is missing a reference from previous page (1:250256). Possible chain linkage problem. ... CHECKDB found 0 allocation errors and 4 consistency errors in database 'Company'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Company). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Before anything else, have you tried re-running the check on the original database? It might just have been a memory glitch. What can we tell from the errors? They are all index ID 1 which means clustered indexes. That first error 8928 says that page 1:250256 is damaged. You could use the object ID to see which table it is. It looks to me like the other errors are fallout from that because they talk about other pages that reference or are referenced by the the damaged page. It would be interesting to get that page out of both databases and compare them. You can use DBCC PAGE for that (
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/). You could save both to a file and use a diff tool to compare them. Just speculation but could this be a disk error? The data got corrupted on the disk but when you restored it, the redo phase of recovery re-applied a transaction from the log which corrected the errors. Have you checked the error log for 825 errors? (
http://www.sqlskills.com/blogs/paul/a-little-known-sign-of-impending-doom-error-825/) Maybe the data is fine on disk but somehow got corrupted in memory after the page was read in. Paul Randal talks here (
http://www.sqlskills.com/blogs/paul/misconceptions-around-corruptions-can-they-disappear/) about how corruption errors can disappear if a corrupt page gets de-allocated. Perhaps your corrupt pages contain all logically deleted rows which the ghost cleanup process got rid of on the 2nd database, thereby making the corruption go away. Lots of ideas but nothing conclusive. Hope this helps a bit though.
if you dont have valid backup you might as well try to repair the database using below command ALTER DATABASE DB_NAME SET SINGLE_USER; GO DBCC CHECKDB (N'DB_NAME', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO