September 8, 2022 at 10:56 am
Hi,
If there are any consistency errors in the database how can we fix database consistency errors after the power loss/Disaster recovery?
September 8, 2022 at 12:37 pm
Do you mean errors which are reported via DBCC CHECKDB?
If so the official line from Microsoft is to restore to your last known good backup.
You may attempt the repair_rebuild and/or repair_allow_data_loss options as a last resort, but they are just that a last resort, backups should always be attempted first.
September 8, 2022 at 4:30 pm
Run CHECKDB to determine errors. Backup the existing db to make sure you'll never be worse off than you are now.
If an error is in a non-clustered index, simply drop the index and recreate it.
If an error is in a clustered index, it's not as easy to fix. You can try copying the data to another table, going around any error(s) as best you can. You can also try dropping the clustered index, which may or may not work (likely won't, but worth a shot).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2022 at 4:43 pm
Do you mean errors which are reported via DBCC CHECKDB?
If so the official line from Microsoft is to restore to your last known good backup.
You may attempt the repair_rebuild and/or repair_allow_data_loss options as a last resort, but they are just that a last resort, backups should always be attempted first.
Although I agree that the best option is to restore from a known good backup - and then applying all transaction log backups up to the current point in time, there are cases where consistency type errors can be corrected without having to go to that extreme.
For example, if the consistency errors are on a non-clustered index then dropping and recreating that index can resolve those issues. In fact, the repair option would probably just rebuild the index for you with no data loss - but I would rather do that myself so I am not 100% sure that is the case.
One other note: if you have encountered these issues after a power outage, then you probably have additional issues with your storage that need to be addressed. If those are not addressed then something like this will happen again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply