asked
Troubleshooting Corrupted DB
All so I come to you with the below errors. We have a medical practice who has had their EHR sql db corrupted twice over a two week period. We were just out the woods with a fully operational database from backup after the machine blue screened. This weekend it corrupted again. The vendor is pointing at a scheduled reboot where the system went down gracefully but that is grasping. SQL Version: 11.0.6020.0 Date 10/17/2016 7:00:00 PM Log Job History (DBCC CHECKDB - one time run) Step ID 1 Server Server1 Job Name DBCC CHECKDB - one time run Step Name dbcc check db Duration 01:17:16 Sql Severity 16 Sql Message ID 2528 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message > Executed as user: DOMAIN\\user. ...nit ID 281475513581568 (type Unknown), page ID (3:1371413) contains an incorrect page ID in its page header. The PageId in the page header = (0:547477408). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281475513581568 (type Unknown), page ID (3:2636533) contains an incorrect page ID in its page header. The PageId in the page header = (0:22183232). [SQLSTATE 42000] (Error 8909) Table error: Object ID 425103534, index ID 3, partition ID 72057610826547200, alloc unit ID 72057611209605120 (type In-row data), page ID (6:543355) contains an incorrect page ID in its page header. The PageId in the page header = (14:579813). [SQLSTATE 42000] (Error 8909) Object ID 481435718, index ID 0, partition ID 72057594902347776, alloc unit ID 72057594909687808 (type In-row data): Page (6:543355) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 503724897, index ID 10, partition ID 72057624004788224, alloc unit ID 72057624853741568 (type In-row data): Page (11:3489604) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Table error: Object ID 613577224, index ID 5, partition ID 72057624563810304, alloc unit ID 72057625433538560 (type In-row data), page ID (9:1277794) contains an incorrect page ID in its page header. The PageId in the page header = (11:1941694). [SQLSTATE 42000] (Error 8909) Object ID 723533661, index ID 0, partition ID 47417502007296, alloc unit ID 47417502007296 (type In-row data): Page (3:1371413) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 739533718, index ID 0, partition ID 48466081742848, alloc unit ID 48466081742848 (type In-row data): Page (9:1277794) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Table error: Object ID 858485066, index ID 0, partition ID 72057594928431104, alloc unit ID 72057594935771136 (type In-row data), page ID (3:2069044) contains an incorrect page ID in its page header. The PageId in the page header = (3:791523). [SQLSTATE 42000] (Error 8909) Object ID 947534459, index ID 1, partition ID 72057601499594752, alloc unit ID 72057601529413632 (type In-row data): Page (3:2069044) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Table error: Object ID 1216722316, index ID 3, partition ID 72057601063518208, alloc unit ID 72057601077673984 (type In-row data), page ID (3:70175) contains an incorrect page ID in its page header. The PageId in the page header = (11:5090712). [SQLSTATE 42000] (Error 8909) Object ID 1317579732, index ID 1, partition ID 72057599692374016, alloc unit ID 72057599642894336 (type In-row data): Page (3:70175) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 1317579732, index ID 1, partition ID 72057599692374016, alloc unit ID 72057599642894336 (type In-row data): Page (3:2636533) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 1406680109, index ID 1, partition ID 373663164334080, alloc unit ID 373663164334080 (type In-row data): Page (3:1639022) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 1406680109, index ID 1, partition ID 373663164334080, alloc unit ID 373663164334080 (type In-row data): Page (3:3555090) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 1406680109, index ID 1, partition ID 373663164334080, alloc unit ID 373663164334080 (type In-row data): Page (6:1085239) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928) Object ID 1406680109, index ID 1, partition ID 373663164334080, alloc unit ID 373663164334080 (type In-row data): Page (7:982708) could not be processed. See other errors for details. [SQLSTATE 42000] (Error 8928)... The step failed.
A clean reboot should not be causing corruption of the pages. Usually it's a I/O storage issue where the page is being written to and something happens to cause the write to not complete. I personally have never had that happen and I've rebooted servers all the time. Is it always the same database?
Well it's the vendors recovery strategy. We backup nightly with Veeam. They manage the SQL aspect. It's unorthodox to say the least. They have the DB set to Full recovery and take hourly log backups but at midnight they run maintenance task that switches to simple recovery and flushes the logs. They then restart the LSN chain with a full backup.
Yeah I dont understand the method to the madness. Other than its the easiest way. They do a lot of reconfig statements in there maintenance plans that change MAXDOP and other settings. I am not a DBA just someone with a bit of understanding of SQL. I try and stick to Ola Hallegren and let the poeple who know what they are doing write the correct logic into a maintenance plan.
I am looking through the logs but its hard to pinpoint. The SAN it lives on houses over 30 VM's some sql some other applications and no issues so hard to pinpoint.
So microsoft was no help. But I did find this in the log the night of the reboot. ![alt text][1] [1]: /storage/temp/3740-screen-shot-2016-10-19-at-102817-am.png
Anyone seen or have info on the below DBCC error? This was the output following the DBCC Repair_allow_data_loss ![alt text][1] [1]: /storage/temp/3747-screen-shot-2016-10-20-at-91811-am.png
Just curious, are you working on the corrupted database or a copy of it? If the former, I'd probably suggest (might be too late) that you make a copy of the database and then see if you can fix the corruption there. This would leave the main DB intact (corruption an and all).
Clearly something bad has happened to this database. I think you should give Microsoft support a call.
19 People are following this question.
