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 12:38 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 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 #861974
Posted Monday, February 8, 2010 3:50 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: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #862103
Posted Monday, February 8, 2010 4:49 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 9:50 PM
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 !
Post #862132
Posted Tuesday, February 9, 2010 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 2:23 PM
Points: 8, Visits: 83
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.
Post #862428
Posted Tuesday, February 9, 2010 3:58 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Sunday, December 14, 2014 5:56 PM
Points: 114, Visits: 459
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
Post #862800
Posted Tuesday, February 9, 2010 5:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
@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
Post #862849
Posted Tuesday, February 9, 2010 6:16 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 9:50 PM
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 .........

CodeOn (better !)

Post #862857
Posted Tuesday, February 9, 2010 7:21 PM


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
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... :)


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #862888
Posted Tuesday, February 9, 2010 9:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Hi,

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

Koteswar Rao
Post #862926
Posted Tuesday, February 9, 2010 9:50 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 9:50 PM
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
Post #862929
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse