question

gawsandy avatar image
gawsandy asked

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.
corruptioncheckdb
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
just curious: did you use the CHECKSUM option on the backup command?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nope, just BACKUP DATABASE 'database' TO DISK = c:\file WITH NOFORMAT, NOINIT, NAME = name, SKIP, NOREWIND, NOUNLOAD, STATS = 10'
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David, Thanks for the ideas. Yes, I did run a checkdb on the original database after I restored it and it came back with no errors. I was originally thinking a disk error, but A memory glitch makes more sense because of how the issue presented. We first saw the error when running a crystal report. When the report was run, crystal would alert us and there would be an event in the error log. But, if I took the query that report was running and ran it SSMS, it ran fine. I wish I would have restarted the server prior to the restore to test that theory. I appreciate your response because although my issue is resolved, it just seems as if it was way too easy (not that I'm ungrateful).
1 Like 1 ·
jason_clark03 avatar image
jason_clark03 answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have valid backups. I was wondering how a simple restore, using a backup taken after the consistency error appeared, fixed the consistency errors.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.