February 16, 2010 at 2:45 pm
GT-897544 (2/16/2010)
GilaMonster (2/16/2010)
GT-897544 (2/16/2010)
And by dbcc I mean with “DBCC CHECKTABLE” REPAIR_REBUILD. If that did not complete successful after two or three times I would then use “REPAIR_ALLOW_DATA_LOSS” option.Oh for crying out loud!
It is impossible to recommend a repair level for corruption without seeing the full and complete output of CheckDB WITHOUT a repair level.
If repair rebuild doesn't do the job the first time, it won't do it the second, third, forth or 500th time. If a CheckDB returns that the minimum leevl to repair is something other than REPAIR_REBUILD, it is senseless and useless to suggest REPAIR_REBUILD
I should also mention that in Emergency Mode, the only repair level that's allowed is REPAIR_ALLOW_DATA_LOSS. That's what I referred to earlier when I mentioned "Emergency mode repair" It's almost the last resort and should only be done if there's no database backup to restore from.
It is not the first thing that should be tried with a suspect or damaged database. It's the last thing and if it fails, then it's data extraction time.
For corruption, this may be worth a read. http://www.sqlservercentral.com/articles/65804/ Unfortunately there isn't a good article on suspect databases yet, though I recommend Paul Randal's blog.
Alright Gail, What you do in this situation? Don't you take action first and bring database online first rather than trying to figure what error messages say? Can't we restore if we have latest backup first and bring the database online and do whatever you want to later?
EnjoY!
Sure you can. And then when you find out that the problem is being caused by a failure at the I/O level, you now have two problems to solve instead of one.
"Solving" without investigating is called "leaping before you look", and it's been recognized as a primary cause of human failure and waste for several thousand years.
The proper action sequence is Observe, Orient, Decide, Act. This is well codified and well understood. Bing/Google that phrase and/or "OODA" to get more information on how it works, why it works, and why deviation from it fails.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 16, 2010 at 2:45 pm
I do agree with you guys, we need to find a problem. But how good is keeping database offline\Suspect mode and looking for solution and keeping business down for hours until you find a solution? Lets see what is Gail suggestion on this.
EnjoY!
February 16, 2010 at 2:49 pm
Because it isn't likely to be down for hours. Reading the errorlog takes moments. And most causes of suspect databases can be resolved fairly quickly. But how do you know what to do without any idea of what is causing the problem?
CEWII
February 16, 2010 at 2:50 pm
If one reacts and shoots from the hips, troubleshooting will become more complicated.
Find out the cause first. Fix the root cause. Troubleshooting is a progression of fact finding and decisions and not an assumption based technique. Once one has determined what the real issue is, one can fix that issue. That may save a lot of pain and get the database online more quickly.
Even from emergency mode, I am not going to jump immediately to a repair_allow_data_loss.
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 16, 2010 at 2:56 pm
GT-897544 (2/16/2010)
I do agree with you guys, we need to find a problem. But how good is keeping database offline\Suspect mode and looking for solution and keeping business down for hours until you find a solution? Lets see what is Gail suggestion on this.EnjoY!
Most of us already know what Gail would do first; check the logs, SQL Server, Application, System, and any others that may provide information on what happened to cause the databases to go suspect.
February 16, 2010 at 2:56 pm
GT-897544 (2/16/2010)
I do agree with you guys, we need to find a problem. But how good is keeping database offline\Suspect mode and looking for solution and keeping business down for hours until you find a solution? Lets see what is Gail suggestion on this.EnjoY!
My suggestion is, if it's a business-critical database of that sort, have a DR copy available. Mirroring, log-shipping, replication, etc., can all make the downtime for the business approach 0.
My second suggestion is, don't take hours to check the log file. It should take a few minutes at most. Then you'll know what you're doing next. If you aren't familiar with finding issues in the error logs, then you need to become so.
Ideally, the business should never need to know anything went wrong till you're telling the managers that, "the DR plan worked perfectly, there was no interruption of business, and we're now back on the primary servers after replacing the faulty hardware".
If a suspect database, or even a crashed server, results in the business being locked down for hours, or even minutes, you're really not doing your job as a DBA.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 16, 2010 at 2:57 pm
Elliott W (2/16/2010)
This whole post makes me laugh..Basic Troubleshooting 101:
1. Find out WHY you have the problem in the first place
It is clear that GT-897544 has very little real world experience, and a data extract is right near the bottom of the list for solving this problem.
The problem could be as simple as a disk is offline, but nobody will know until the cause has been looked at.
Research FIRST, then ACT. How can anyone who what is the right course of action until they have some idea of the problem.
To the original poster, look at the error logs, they will almost certainly tell you exactly why the database is suspect. Once have those messages you can post them and we can help you.
CEWII
I don't have 15 - 20 years as you guys do. If the Disk is offline Server team had notified the DBA's in first place. If you don't have the back up won't you extract the data? Will you wait until database files are completely corrupt?
EnjoY!
February 16, 2010 at 3:01 pm
GT-897544 take a break. You're like a bull in a china shop. Stop with all the IFs. Like a few of us have said, step #1 find out what the problem is.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 16, 2010 at 3:03 pm
GT-897544 (2/16/2010)
Alright Gail, What you do in this situation? Don't you take action first and bring database online first rather than trying to figure what error messages say? Can't we restore if we have latest backup first and bring the database online and do whatever you want to later?
I'm not in the habit of acting quickly without thought then paying the price later. I'm used to working on DBs so big that a restore takes 3-4 hours at best. Hence saving the five minutes that reading the log will require is not a good idea.
Starting a 3 hour restore and then finding that all that was needed was a restart of SQL is something that we, in this part of the world, refer to as a 'career-limiting move'
Step 1. Check the SQL error log, look for the messages that say why SQl is marking the DB suspect. It always has a reason. This shouldn't take more than a couple of minutes.
Step 2: Decide on an appropriate course of action. Depending why the DB was marked suspect, this may be as simple as restarting the SQL Service (if a file was inaccessible when SQL started, perhaps due to antivirus) or it may require restoring from backup
Step 3: Perform the action selected above.
If at this point it becomes clear that there is no backup or all the backups are corrupt (both of which indicate that someone's not been doing their job) then and only then is something like Emergency mode repair considered. If that fails then the extraction of what data is still accessible is indicated.
It is critically important in a disaster situation (and I would consider a suspect DB a disaster) not to panic, not to rush quick, half thought through 'fixes' out, not to do the first thing that comes to mind, but to carefully decide on appropriate actions for the given situation and implement those actions carefully.
I've seen what should have been a 5-hour database restore turn into a 9 hour database restore because the DBA who was doing the restore didn't want to waste the few minutes it would have taken for another person to double check what he was doing. As a result he restored the differential backup WITH RECOVERY instead of WITH NORECOVERY forcing the entire restore process to be started over from scratch. Net result: 2 minutes saved, 4 hours lost.
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 16, 2010 at 3:05 pm
GT-897544 (2/16/2010)
Elliott W (2/16/2010)
...CEWII
I don't have 15 - 20 years as you guys do. If the Disk is offline Server team had notified the DBA's in first place. If you don't have the back up won't you extract the data? Will you wait until database files are completely corrupt?
EnjoY!
If no backup exists, then yes the data would be extracted. This, as has been said, is a very last step.
Your question on waiting for full corruption - No. Nobody here has said anything paramount to this.
Don't confuse troubleshooting with lack of action. Troubleshooting the situation is actively and progressively resolving the problem.
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 16, 2010 at 3:07 pm
GT-897544 (2/16/2010)
Elliott W (2/16/2010)
This whole post makes me laugh..Basic Troubleshooting 101:
1. Find out WHY you have the problem in the first place
It is clear that GT-897544 has very little real world experience, and a data extract is right near the bottom of the list for solving this problem.
The problem could be as simple as a disk is offline, but nobody will know until the cause has been looked at.
Research FIRST, then ACT. How can anyone who what is the right course of action until they have some idea of the problem.
To the original poster, look at the error logs, they will almost certainly tell you exactly why the database is suspect. Once have those messages you can post them and we can help you.
CEWII
I don't have 15 - 20 years as you guys do. If the Disk is offline Server team had notified the DBA's in first place. If you don't have the back up won't you extract the data? Will you wait until database files are completely corrupt?
EnjoY!
I think you miss the major point here, you have no idea what is wrong until you look, it doesn't take long. Start at the SQL errorlog. I'd go as far as saying that there is a 99% chance that it will tell you what it thinks the problem is. I've actually spent more time typing out the sequence than it would take to DO what I described. Once you have that information in hand you can make a decision on your next step. You CANNOT POSSIBLY KNOW what will happen if you haven't even looked to see what might be wrong. Assuming ANYTHING at this point is a BAD PLAN. You don't have any facts about the problem, until you do you are just as likely to cause a new problem as fix one.
Getting the database up IS important, but data integrity is the MOST important.
This is all basic troubleshooting and is not specific to SQL. Lets make an analogy, your car dies on the road, do you just change out the engine, or do you look at the gas gauge and find out you were a bonehead and ran out of gas?
CEWII
February 16, 2010 at 3:14 pm
I agree Gail, Only thing made to reply was when you all said data extract is not possible with suspect database. Because i came across those situations. My apologies if i had hurt anyone here, lets see how poster deals all this together. Let focus on other postings.
You all have great night!
EnjoY!
February 16, 2010 at 3:23 pm
Elliott W (2/16/2010)
GT-897544 (2/16/2010)
Elliott W (2/16/2010)
This whole post makes me laugh..Basic Troubleshooting 101:
1. Find out WHY you have the problem in the first place
It is clear that GT-897544 has very little real world experience, and a data extract is right near the bottom of the list for solving this problem.
The problem could be as simple as a disk is offline, but nobody will know until the cause has been looked at.
Research FIRST, then ACT. How can anyone who what is the right course of action until they have some idea of the problem.
To the original poster, look at the error logs, they will almost certainly tell you exactly why the database is suspect. Once have those messages you can post them and we can help you.
CEWII
I don't have 15 - 20 years as you guys do. If the Disk is offline Server team had notified the DBA's in first place. If you don't have the back up won't you extract the data? Will you wait until database files are completely corrupt?
EnjoY!
I think you miss the major point here, you have no idea what is wrong until you look, it doesn't take long. Start at the SQL errorlog. I'd go as far as saying that there is a 99% chance that it will tell you what it thinks the problem is. I've actually spent more time typing out the sequence than it would take to DO what I described. Once you have that information in hand you can make a decision on your next step. You CANNOT POSSIBLY KNOW what will happen if you haven't even looked to see what might be wrong. Assuming ANYTHING at this point is a BAD PLAN. You don't have any facts about the problem, until you do you are just as likely to cause a new problem as fix one.
Getting the database up IS important, but data integrity is the MOST important.
This is all basic troubleshooting and is not specific to SQL. Lets make an analogy, your car dies on the road, do you just change out the engine, or do you look at the gas gauge and find out you were a bonehead and ran out of gas?
CEWII
I agree with out Elliott, See the posting namasivah mention he had already set it to Emergency mode, that why i mentioned extract data in first place. As a DBA he would have checked the SQL errors logs and Windows event logs, may he didn't post here. I am with you on that first check Error logs, i would do the same. Any how lets get this database working.
EnjoY!
February 16, 2010 at 3:26 pm
GT-897544 (2/16/2010)
Elliott W (2/16/2010)
This whole post makes me laugh..Basic Troubleshooting 101:
1. Find out WHY you have the problem in the first place
It is clear that GT-897544 has very little real world experience, and a data extract is right near the bottom of the list for solving this problem.
The problem could be as simple as a disk is offline, but nobody will know until the cause has been looked at.
Research FIRST, then ACT. How can anyone who what is the right course of action until they have some idea of the problem.
To the original poster, look at the error logs, they will almost certainly tell you exactly why the database is suspect. Once have those messages you can post them and we can help you.
CEWII
I don't have 15 - 20 years as you guys do. If the Disk is offline Server team had notified the DBA's in first place. If you don't have the back up won't you extract the data? Will you wait until database files are completely corrupt?
EnjoY!
If you run into that situation, the proper solution is for your manager to fire you on the spot before you cause even more damage.
From this post and your others, I seriously think you'll be happier and more satisfied in a different field of work. Being a DBA is mostly a matter of being organized, planning ahead, having your solutions worked out before the problem comes up in the first place, and knowing every detail of troubleshooting technique. You don't seem to want to do that kind of thing.
This isn't an attack on you. It's my best advice. It could completely be wrong, since I don't know you personally. But do think about it.
The goal of a professional DBA is to be as bored as possible at work. If your job requires any level of adrenaline or excitement at all as a DBA, you're doing it wrong, or you're taking over from someone who did it wrong.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 16, 2010 at 4:03 pm
GSquared (2/16/2010)
GT-897544 (2/16/2010)
Elliott W (2/16/2010)
This whole post makes me laugh..Basic Troubleshooting 101:
1. Find out WHY you have the problem in the first place
It is clear that GT-897544 has very little real world experience, and a data extract is right near the bottom of the list for solving this problem.
The problem could be as simple as a disk is offline, but nobody will know until the cause has been looked at.
Research FIRST, then ACT. How can anyone who what is the right course of action until they have some idea of the problem.
To the original poster, look at the error logs, they will almost certainly tell you exactly why the database is suspect. Once have those messages you can post them and we can help you.
CEWII
I don't have 15 - 20 years as you guys do. If the Disk is offline Server team had notified the DBA's in first place. If you don't have the back up won't you extract the data? Will you wait until database files are completely corrupt?
EnjoY!
If you run into that situation, the proper solution is for your manager to fire you on the spot before you cause even more damage.
From this post and your others, I seriously think you'll be happier and more satisfied in a different field of work. Being a DBA is mostly a matter of being organized, planning ahead, having your solutions worked out before the problem comes up in the first place, and knowing every detail of troubleshooting technique. You don't seem to want to do that kind of thing.
This isn't an attack on you. It's my best advice. It could completely be wrong, since I don't know you personally. But do think about it.
The goal of a professional DBA is to be as bored as possible at work. If your job requires any level of adrenaline or excitement at all as a DBA, you're doing it wrong, or you're taking over from someone who did it wrong.
What do you mean by organized? Should alwayd DBA's check blogs before they do there jobs, our team never did that, we always knew what we were doing and not posting questions waiting for so called organized DBA's replies. Did you see database in suspect mode ever in your career? If so how did you fix that?
EnjoY!
Viewing 15 posts - 31 through 45 (of 62 total)
You must be logged in to reply to this topic. Login to reply