Database Corruption

Question

Hello:

We have a manufacturing application connecting to a SQL Database and endusers are experiencing errors in a specific part of the app. There are several differnt modules, financial, shipping, partnumbers and others. Only the part numbers seem to be affected. The SQL server event logs are showing error 824 ...error that threatens database integrity ....

When I run CHECKDB against the database I get:

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:700540) 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:700541) 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 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data), page (1:700539). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1.
Msg 8928, Level 16, State 1, Line 1
Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data): Page (1:700539) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data): Page (1:700540) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data): Page (1:700541) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Change_History' (object ID 156579646).
CHECKDB found 0 allocation errors and 6 consistency errors in database 'PRODUCTION'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PRODUCTION).

It was most likely caused by a file level restore after a ransomware attack and was advised to to a repair REPAIR_ALLOW_DATA_LOSS on a new copy of the database. Since I am a novice SQL admin and have very limited knowledge, what wpould be my next plan of action? What would be the likelyhood that only the part that they are having errors on would experience?
TIA

Answer 1

This kind of corruption is always due to physical hard drive problems. Having the time 12 hours different did not cause this issue.

You need to investigate the hard drives.

As for fixing the issue, you need to restore a good backup and reenter whatever you can. If you recover with data loss, it is very difficult to know what has been lost. The errors my go away, but you will not really know what is missing.

Answer 2

Hi @Mark Dudley ,

First of all, I want to ask, is there any good backup of PRODUCTION? If so, please restore it, this is always the best way.
Please see this Microsoft document: DBCC CHECKDB

Use the REPAIR options only as a last resort. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup.
Since DBCC CHECKDB with any of the REPAIR options are completely logged and recoverable, Microsoft always recommends a user use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command) so that the user can confirm he/she wants to accept the results of the operation. Then the user can execute COMMIT TRANSACTION to commit all work done by the repair operation. If the user does not want to accept the results of the operation, he/she can execute a ROLLBACK TRANSACTION to undo the effects of the repair operations.

And this: Troubleshoot database consistency errors reported by DBCC CHECKDB

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thanks for your reply. Unfortunately I do not have a native SQL backup, but I do have daily backuks from my backup software.

After doing maore investigation the corruption seems to be caused by the server time being off by 12 hours. The SQL server is running as a VM on a HYPER-V server that got rebooted. The SQL server itself was completely shut down befpre the reboot.

The first error entry in the log file was 7:34am on 12/17 and the server was rebooted the day before around the end of the work day.

Would a good option be to copy the database and then run the REPAIR on the copy to see what happens?

Answer 4

Thanks to Erland, Seeya and Tom. I was able to engage one of the consultants from the application side luckily took only an hour. The Solution was to run the repair. They surmised that it was a result of an improper shutdown and perhaps a power failure. They were certain that the repair was successful and no data was lost since it was only happening with one module. I will continue to diligently monitor the logs and with the consultants help we are running multiple SQL backups during the day. Another lesson here is never to use dynamically expanding disks in a Hyper-V solution for SQL.

Best regards,
Mark