February 16, 2011 at 4:20 am
GilaMonster (2/15/2011)
Brandie Tarvin (2/15/2011)
BUT, the reason I ask about the database is that if it's Suspect, there may be other options for you that don't require a restore. Restores should always be a last resort.Not at all. Repairs are a last resort, because of the potential data loss.
Ok. I admit I worded my response badly. What I meant to say was "don't restore just yet, we need more information because there may be other ways to fix this."
But you apparently got to that in further responses.
February 16, 2011 at 4:41 am
master0ONLINE
tempdb0ONLINE
model0ONLINE
msdb 0ONLINE
DBAAdmin 0ONLINE
another database not showing any thing.
Regards
venkat
February 16, 2011 at 4:43 am
crazy4sql (2/16/2011)
if we select database it's showing error. database is not operational
what Gail is referring is to run this query in master database:-
select name, state,state_desc from sys.databases
and report about your database stats.
In addition open the sQL error log and find ALL messages relating to this database and post them.
Currently we do not have anywhere close to enough information to diagnose a cause. Without a cause we cannot offer a proper solution.
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, 2011 at 4:44 am
kvr1985 (2/16/2011)
master0ONLINEtempdb0ONLINE
model0ONLINE
msdb 0ONLINE
DBAAdmin 0ONLINE
another database not showing any thing.
If you open object explorer, is the database listed there?
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, 2011 at 4:49 am
Brandie Tarvin (2/16/2011)
Ok. I admit I worded my response badly. What I meant to say was "don't restore just yet, we need more information because there may be other ways to fix this."But you apparently got to that in further responses.
That I will definitely agree with. Part of the 'don't panic' and 'make educated decision as to a fix' from that presentation last week.
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, 2011 at 5:22 am
after answering the still open questions fase and suggestion
and
before restoring backups.
you can always try if you got the possibility to restart SQL server and hoping it start rolling back.
February 16, 2011 at 5:28 am
Marco V (2/16/2011)
after answering the still open questions fase and suggestionand
before restoring backups.
you can always try if you got the possibility to restart SQL server and hoping it start rolling back.
Not yet, he shouldn't. That might cause worse problems than doing nothing at all. In fact, if the database is online and suspect, a restart might make the db completely unrecoverable.
I learned that from Gail.
February 16, 2011 at 5:37 am
Brandie Tarvin (2/16/2011)
Marco V (2/16/2011)
after answering the still open questions fase and suggestionand
before restoring backups.
you can always try if you got the possibility to restart SQL server and hoping it start rolling back.
Not yet, he shouldn't. That might cause worse problems than doing nothing at all. In fact, if the database is online and suspect, a restart might make the db completely unrecoverable.
I learned that from Gail.
true, thats why addressed in my post it should be done when he is at the point of restoring
February 16, 2011 at 5:39 am
Sorry. The spelling in your post confused me. All I could understand was "after you answer the question, try restarting."
February 16, 2011 at 5:47 am
Marco V (2/16/2011)
true, thats why addressed in my post it should be done when he is at the point of restoring
The only time one should restart SQL as part of fixing a damaged database is when due investigation has shown that the cause is something like a file inaccessible at the time SQL started. Otherwise restarting sQL is a waste of time at best and further damaging at worst.
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, 2011 at 5:54 am
Brandie Tarvin (2/16/2011)
In fact, if the database is online and suspect, a restart might make the db completely unrecoverable.
Not quite.
Restarting SQL when there's a corrupt database could take the DB from corrupt but online and usable to suspect and unavailable. When SQL restarts it runs crash recovery on the databases. If it runs crash recovery and that recovery fails due to corruption, the DB will be marked suspect.
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, 2011 at 6:10 am
GilaMonster (2/16/2011)
Brandie Tarvin (2/16/2011)
In fact, if the database is online and suspect, a restart might make the db completely unrecoverable.Not quite.
Restarting SQL when there's a corrupt database could take the DB from corrupt but online and usable to suspect and unavailable. When SQL restarts it runs crash recovery on the databases. If it runs crash recovery and that recovery fails due to corruption, the DB will be marked suspect.
So, back to the statement you made right before this, how does the crash recovery cause further damage to a database that's already marked suspect?
I'm just trying to get this all straight in my head.
February 16, 2011 at 6:48 am
Brandie Tarvin (2/16/2011)
So, back to the statement you made right before this, how does the crash recovery cause further damage to a database that's already marked suspect?
It won't. Suspect is as bad as it can get.
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, 2011 at 6:54 am
Thank you for clarifying, Gail. That helps.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply