February 8, 2010 at 10:20 am
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. ...:-D"
February 8, 2010 at 11:39 am
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[/url]
Learn Extended Events
February 8, 2010 at 12:20 pm
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'.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 8, 2010 at 12:23 pm
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.
February 8, 2010 at 12:26 pm
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"!
February 8, 2010 at 12:38 pm
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
February 8, 2010 at 3:50 pm
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.
February 8, 2010 at 4:49 pm
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 !
February 9, 2010 at 7:38 am
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.
February 9, 2010 at 3:58 pm
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
February 9, 2010 at 5:58 pm
@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
February 9, 2010 at 6:16 pm
@ 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 !)
February 9, 2010 at 7:21 pm
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: 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. ...:-D"
February 9, 2010 at 9:44 pm
Hi,
This is artice is very good and usefull in solving .............thanks
Koteswar Rao
February 9, 2010 at 9:50 pm
Go here, look listen and learn.
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
😛
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply