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.

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.sysdatabasesSET Status = 24WHERE [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.

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 !

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.

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.

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

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

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