Click here to monitor SSC
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
Joan OBryan (2/8/2010)
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.


That is exactly how you do fix the problem where the files were inaccessible (for whatever reason) when SQL started and you have rectified the problem so that the files are accessible. By taking the DB offline and bringing it online, you're allowing SQL to try again to find and open the files. A restart of the SQL service will achieve the same thing.

Note, this is for when the DB is suspect (or more likely recovery_pending) due to inaccessible files.


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


SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Joan OBryan (2/8/2010)

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.


Not always it would work. I had a database suspect just 10 - 12 days back and I tried to change the status to 24 (recommended in most sql sites)
UPDATE master.dbo.sysdatabases
SET Status = 24
WHERE [Name] = 'MyDatabaseName'
GO

It did not work. Then tried offline and online method and it also did not work. with recovery method had no luck. Last option I had and did was to copy and paste data and log files on a different path and then drop suspected database. And then attached copied files giving new database name and then mapped all users and logins.

It worked like a charm.

I agree with most of the comments but there is no hard code rule guaranteed for getting suspected database online. It depends what ever method works for you.

Manager and users just want their database and they give a damn about how DBA gets it back,

Cheers.

By the way, its sql server 2000 sp4. Yeah, we still had some running on 2000.

SQL DBA.
Malcolm Daughtree
Malcolm Daughtree
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 335
Sorry Guys and Gals but I couldn't stay quiet over this.
1. If you move Data files when in 'off line' mode it should have been communicated to your stakeholders that you were doing it and "Code red" irrelevant. “Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file” just makes me angry where is your change control and why didn’t you backup the Database before taking it off line.
2. “SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool” WTF. What other tool should be accessing your Datafiles apart from SQL Server and related services. And you have Anti-virus checking your data files, heaven help us all !!
3. Database is in suspect because of a corrupted transaction. “The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back” Now unless the undo and redo phase of the SQL server recovery fails you have bigger problems than a dodgy transaction and I hope you have a better backup strategy..
And the comment regarding the use of DBCC commands “I recommend trying all other possible options including calling Microsoft Support before executing below steps.” Read books online. Have you tried ringing microsoft support ??
4. I happen to agree on the probable cause, in theory, in practice anything this important to cause a “Code Red” would be on a highly available server or other enrolled in one of several scenarios the prevent this from occurring.

And as foot note: Following, or at least recognizing, best practices and change control this type of situation should be a rare occurance. I have been doing this for a Long, Long time and suspect databases are RARE !
osgcurt
osgcurt
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 86
You know an article is good if you can relate to the discussion and it gets a conversation going.

Even though there is some disagreement with the information provided, it is often the case that the writer is basing his statements on real world experience.

That being said, it is often the case that two techs can be "correct" in different context. But having experiences these issues mentioned in the article, I appreciate the information to help as a guide when the troubleshooting mind is harassed by fatigue and pressure to get production back on line.

Good job to all the contributors on this.
Greg Milner
Greg Milner
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 491
If you make the mistake of detaching a suspect databases (as I did in my younger days) and you are pretty sure it IS corrupt -- since you cannot re-attach it and you are basically hosed -- a good tool for getting out all the possible data from and offline .MDF file is "Recovery for SQL Server." I used it and it worked really well. As I recall, it's pretty cheap, too.

Cheers.



G. Milner
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
@gdmilner That's not true. It's pretty well known how to attach a detached suspect database back into SQL Server in all versions, using a dummy database. Plenty of sites document the procedure (with varying degrees of correctness).

For the correct method for doing it in 2005 and 2008, see my blog post that explains a demo I did at TechEd/PASS/etc in 2006-2008: TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database. There's also a video recording of my Corruption Survival Techniqes session from TechEd Europe in 2008 which shows me doing all of this live on stage: TechEd: 80 minute video of Corruption Survival Techniques presentation.

No need to spend money on potentially dodgy software when SQL Server can do it for you for free if you know how.

Potentially dodgy? Yes. I don't know of any of these 'recovery tool' vendors that work in all cases.

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
Malcolm Daughtree
Malcolm Daughtree
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 335
@ Paul R, Thanks for your input. Personally, this article should never have got off the Editors desktop. It is akin to answering "How do I get SQL running faster ?" ANS: add more RAM !! Between losing database files, dodgy Transactions and suspect hardware I think there are deeper issues here and most of which could be prevented by proper Change/ Code control and a little time and effort into capacity planning in the first instance. And if these 4 situations are from "real life" I'll stand in [Insert prominant local feature] shouting beers. And I hate beer ......... :-D

CodeOn :-P (better !)
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 3069
Paul Randal (2/9/2010)
There's also a video recording of my Corruption Survival Techniqes session from TechEd Europe in 2008 which shows me doing all of this live on stage: [url=http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-80-minute-video-of-Corruption-Survival-Techniques-presentation.aspx]TechEd: 80 minute video of Corruption Survival Techniques presentation


This is the video I was referring to in my previous post that everyone should watch multiple times until you have all of the info in it committed to memory. I have watched it at least 5 times myself and the information in it is priceless. Paul also includes the corrupt dbs and code that he addresses in the video as well. One of the best videos out there on database corruption, bar none! Plus Paul goes much further, covers more errors, and in much more depth than this article covered. If you are a production DBA concerned with future database corruption then this video is a must see... It is a little on the lengthy side (80 minutes) but well worth the time put into watching it... It's like Paul says "Database corruption will happen to you sooner or later in your career" This video will go a long way to helping you prepare for it... 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"
y.koteswarrao-652921
y.koteswarrao-652921
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 199
Hi,

This is artice is very good and usefull in solving .............thanks

Koteswar Rao
Malcolm Daughtree
Malcolm Daughtree
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 335
Go here, look listen and learn.

http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-80-minute-video-of-Corruption-Survival-Techniques-presentation.aspx

I can't say this loud enough

DONT GO ANYWHERE WITHOUT DOING SO. IT IS THE BEST 80 MINUTES YOU"LL EVER SPEND.

Thanks Paul, all DBA's should see this, even the Old Farts like me !!

CodeOn
:-P
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