A guide to recover a database out from Suspect mode

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by UTSAB... » A guide to recover a database out from...

43 posts, Page 2 of 5

««1 2 3 4 5 »»»

A guide to recover a database out from Suspect mode

Rate Topic

Display Mode

Topic Options

Author

Message

GilaMonster

Posted Monday, February 08, 2010 7:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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

Grant Fritchey

Posted Monday, February 08, 2010 7:42 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187

GilaMonster (2/8/2010)

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #861675

Robert Eder

Posted Monday, February 08, 2010 7:45 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:52 AM
Points: 844, Visits: 96

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

Paul Randal

Posted Monday, February 08, 2010 8:13 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 1,910, Visits: 1,602

@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

don_goodman

Posted Monday, February 08, 2010 9:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 06, 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

TravisDBA

Posted Monday, February 08, 2010 10:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:23 AM
Points: 1,288, Visits: 2,996

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. ... BigGrin

Post #861850

SQLRNNR

Posted Monday, February 08, 2010 11:39 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #861923

rudy - Doctor "X"

Posted Monday, February 08, 2010 12:20 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:11 PM
Points: 3,108, Visits: 2,114

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

Joan OBryan

Posted Monday, February 08, 2010 12:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:43 PM
Points: 138, Visits: 538

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

DMarvez

Posted Monday, February 08, 2010 12:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:41 PM
Points: 40, Visits: 265

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 »

43 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions