Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

A guide to recover a database out from Suspect mode Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 7:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
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 2008, MVP
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

Post #861659
Posted Monday, February 8, 2010 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,032, Visits: 28,406
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #861675
Posted Monday, February 8, 2010 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:15 PM
Points: 1,332, Visits: 201
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.
Post #861678
Posted Monday, February 8, 2010 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
@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
Post #861706
Posted Monday, February 8, 2010 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:30 PM
Points: 34, 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.
Post #861794
Posted Monday, February 8, 2010 10:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
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....:)

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #861850
Posted Monday, February 8, 2010 11:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #861923
Posted Monday, February 8, 2010 12:20 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 22, 2014 7:46 PM
Points: 3,220, Visits: 2,361
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."
Post #861960
Posted Monday, February 8, 2010 12:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 24, 2014 3:24 PM
Points: 138, Visits: 546
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.
Post #861963
Posted Monday, February 8, 2010 12:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:13 PM
Points: 41, 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"!
Post #861965
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse