April 13, 2016 at 2:49 pm
I have hit this corruption issue lately in one of our databases. I am not sure how it all started, but lately I was seeing a lot of events of the nature as described below. I am not a SQL DBA, but more so an accidental DBA, and the only thing I found on google was to run dbcc checkdb for the corrupted database, and when it ran it suggested me the minimum allowable repair level is with data_loss. I am not sure how much, and what all data would be lost after fixing this issue, and is there any way I can restore some data out of the backups without incurring much loss.
Thanks
April 13, 2016 at 11:38 pm
When this error occurred ? When is the last day your DBCC CHECKDB completed without error. Hope you have clean backup.
What you can do is copy the database to Test Server and run DBCC CHECKDB with allow data loss and see how much data you lost.
If the clean bacckup you have is more latest then can use that.
Below are my findings and i believe the damage is somewhat extensive.
Linkage error for object 1136723102 in clustered and nonclustered index
and in object 1588200708 which is a heap
Wait for the corruption expert. Still in bed i think 😀
April 14, 2016 at 7:59 am
Hopefully you have backups in place. You may need to go to these and be prepared to do a page level restore. This is not an easy operation. Practice several times on a non-production system to be sure you have it down. Then, it's a question of identifying the pages with the issue and restore those pages from backups that have those pages intact.
Be VERY cautious about using the repair function with data loss. The "some" data that you can lose is up to the entire database. It's a non-trivial thing to run this.
You might want to track down a local consultant who can help out. This stuff is very dangerous and if your database is important, you don't want to lose it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 14, 2016 at 9:22 am
In addition to the replies you already have - first, before doing anything at all to change the state of the database, take a full backup with the copy-only option and store that backup in a safe place. Yes, that backup will have the same corruption as the database itself. But if you mess up the rest of the database when you try a repair option that backfires, you can at least get back to the current state.
(And you can also restore that backup on a test system and try the repair/recovery options there first)
April 14, 2016 at 4:02 pm
Watch this:
TechEd: 80 minute video of Corruption Survival Techniques presentation[/url]
Some notes:
check select * from msdb..suspect_pages
Last known good dbcc check can be found by running DBCC DBINFO('[DB_NAME]') WITH TABLERESULTS and looking for dbi_dbccLastKnownGood
...also it looks like some are index ID 2, which you may be able to fix by rebuilding the index.
I've also had some luck fixing allocation errors by simply running "dbcc updateusage ('[DB_NAME]') with count_rows"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply