June 14, 2011 at 6:12 am
I have a SQL2008R2 database that was mirrored. I was about to run some scripts to update some tables, SPs etc, but these were failing with
"An inconsistency was detected during an internal operation. Please contact technical support".
Various attempts at manually updating some of the tables failed, and although I'm happily able to query all data, I can't make any config changes. Any attempt to alter a table, SP, or other object result in the message
"Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG"
This includes DBCC CHECKDB, and I've even tried setting to single-user mode and using the repair options, but all result in the same message. To compound the problem, the only backup I have also contains the corruption.
Any ideas? Thanks in advance for any suggestions...
June 14, 2011 at 6:19 am
Hopefully Gail sees this. She's a super corruption fixer.
She'll probably say to not do anything until she gets a good look at the error that DBCC checkDB returns (with no repair options!).
So having the exact error for her would be helpful. Also any relevant info from error logs.
June 14, 2011 at 6:21 am
Please run this and post the full and complete output.
DBCC CheckDB(<Database name>) WITH No_INFOMSGS, ALL_ERRORMSGS
Do you have a backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 6:47 am
Complete output -
Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Only one backup, restoring from it produces the same result ..
June 14, 2011 at 6:50 am
Fail over to the mirror. CheckDB that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 6:54 am
Same thing on the mirror 🙁
June 14, 2011 at 6:55 am
Do you want the bad news or the worse news?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 6:58 am
Kind of suspected it was an unsolvable one - go on then, hit me with both ..
June 14, 2011 at 7:05 am
This is not repairable in any way. You're going to have to recreate the database from scratch.
Script all objects, most will likely fail because of the schema corruption, I hope you have the database definition in source control (or similar).
Export all data (bcp out). Some may well fail.
Recreate the database from scratch.
Once you've done all that, recreate your mirroring setup and create regular integrity checks. Make sure that you run them often enough that you always have the option of restoring from a clean backup.
Finally, do some root cause investigation on the server, something caused this corruption, most likely something IO related. Check logs, patch, update drivers and firmware.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 7:08 am
Sorry Deorca 🙁
Learn from this as Gail said. Make sure you have a solid backup strategy in place. Run checkdb regularly to catch this corruption asap (hopefully never again).
If you have any questions with all of this, we're more than happy to help.
June 14, 2011 at 7:20 am
Many thanks for your help - this is called learning the hard way, I guess! 😀 Jeez, never again ..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply