SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A guide to recover a database out from Suspect mode


A guide to recover a database out from Suspect mode

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223195 Visits: 46294
A suspect DB has to be set to Emergency mode before CheckDB can be run. There's a brief (one sentence) mention of running CheckDB in Scenario 3, right before the checkDB with repair. It should have been a lot clearer.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98045 Visits: 33014
GilaMonster (2/8/2010)
A suspect DB has to be set to Emergency mode before CheckDB can be run. There's a brief (one sentence) mention of running CheckDB in Scenario 3, right before the checkDB with repair. It should have been a lot clearer.


Ah, I did miss that part, but scenario 3 is not the only place I'd run it, depending on the situation.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Robert Eder
Robert Eder
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2304 Visits: 439
It is worth mentioning that after a repair with data loss, not only is the corrupted record lost, but the whole data page. The good news is that with a good backup strategy, the lost page can be restored from backups.

Considering that the transaction is written to the transaction logs before committed to the database, you may get lucky and have no data loss after restoring the page.
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8126 Visits: 1719
@Robert Not true - the action CHECKDB takes depends on the corruption - there are some cases where I delete just the corrupt record rather than the whole page.

IMHO this article should not have been published in it's current form. It does not explain all the ramifications of going straight to emergency mode repair (running REPAIR_ALLOW_DATA_LOSS in emergency mode), doesn't explain the SUSPECT and RECOVERY_PENDING statuses correctly, and also doesn't advocate attempting an export/import into a new database rather than running repair.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
don_goodman
don_goodman
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 82
This article would be fine except it doesn't cover the ground sufficiently and you could end up restoring a database over new data when there was nothing wrong in the first place.

Before restoring a database that is in suspect mode, check your error log. You may find that a drive has gone offline. You may be able to bring that drive back online with the data file in tact. I have done this with SAN connections that are failing.

It pays to be very thorough before jumping to the conclusion that you need to restore a production database over new data.
TravisDBA
TravisDBA
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3318 Visits: 3069
I agree, which is why I recommended Pauls site. He covers this topic in the kind of detail it deserves. His "In Recovery" series is outstanding as well as a video he did on the topic. Who better to address this very important subject than the man who wrote DBCC CHECKDB? Like Paul states it is not always neceassry to recover or repair the entire database risking unknown data loss Many times it is either a matter of just rebuilding indexes or just finding the offending record(s) and either updating or removing them. This is where DBCC PAGE can come into play. One one of the most important undocumented DBCC commands out there IMHO. Don't cut-off your arm when a band-aid will do....Smile

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66337 Visits: 18570
I think Gail, Grant and Paul have made the counter points very well. It seems that the decision in this article to jump straight to emergency mode recovery was done hastily. More explanation and caution throughout the article is advisable.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Rudyx - the Doctor
Rudyx - the Doctor
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10880 Visits: 2503
While the articles author made a contribution that he thought he was helping his community - the discussion responses speak otherwise. The SSC community stepped in to clarify, augment and correct misinformation in the article. I laud the members of the SSC community that stepped up and openly discussed this article.

A while back there was a rather involved discussion about 'vetting' the QOTD. Maybe now an active 'practice' of a high level 'vetting' for all of the submitted articles for content review should be thought about. While it is great that the 'experience' and 'knowledge' in the community stepped in, just think of the potential damage that may have been mistakenly caused by those more junior thinking the article was 'gospel'.

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Joan OBryan
Joan OBryan
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 552
Utsab, I am very glad you included Possibility #2 in your guide, because like you stated, there are various conditions that can cause a database to be marked "suspect" and most people ignore this benign state in Possibility #2 that the database sometimes falls into when they document how to recover from the problem. Once SQL Server marks a db 'suspect' the worst is usually expected due to all the worst case scenarios that have been documented elsewhere and the fact that your database is no longer accessible, which tends to freak people out.

I have one server that had multiple production databases, but one db would get marked 'suspect' occasionally for just this reason. And the reason is evident when you look at the SQL Error log and you see that when SQL needed access, some other process had it locked. It was then marked as suspect.

The easiest way I found to recover in this scenario was to take the database OFFLINE (not detach!) and then bring it back ONLINE in Enterprise Manager. This worked very well in SQL Server 2000 and didn't affect any of the other databases in the instance. I haven't encountered this kind of suspect db in SQL Server 2005 yet.
DMarvez
DMarvez
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 313
This also goes back to a point made a few months back of being careful on what you deploy based on an article you've read on the web.

This site is a great resource but as with everything out there on the interweb....

"Trust but verify"!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search