August 4, 2011 at 10:25 am
Hi everyone,
What really makes a database go into suspect mode and what steps do I take to get the database out of it?
Thanks.
August 4, 2011 at 10:37 am
August 4, 2011 at 11:01 am
It's caused when SQL either fails to complete a transaction rollback due to corruption or when it start but can't complete crash recovery, usually due to the same.
Fix is restore from backup.
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
August 4, 2011 at 11:04 am
Imran Nadeem (8/4/2011)
HiCould be various reasons; mostly hardware failure or deleted ldf's
A deleted log file (or deleted mdf or ndf) results in a state of RECOVERY_PENDING, not 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
August 4, 2011 at 2:58 pm
Thanks Gail.
August 5, 2011 at 4:44 am
Hi ,
This queries it will help full to recover database from suspect mode .
use master
go
exec sp_resetstatus 'Databasename'
go
alter database Databasename set emergency
go
dbcc checkdb('Databasename')
alter database Databasename set single_user with rollback immediate
dbcc checkdb('Databasename',repair_allow_data_loss)
alter database Databasenameset multi_user
i run the same quiries to recover the database from suspect mode .
HI Gila ,
please advise on this ??
August 5, 2011 at 4:53 am
No, No, No, No, No!!!!!!!!!!!!!!!!!!!!
Emergency mode repair is the VERY LAST RESORT. It's done when NOTHING else works, NOTHING else can be done and there are NO other options. It is absolutely not the first thing that you should try.
As it's name implies, CheckDB with REPAIR_ALLOW_DATA_LOSS allows data loss.
Doing that with absolutely no investigation of the cause of the corruption is incredibly irresponsible. Do you take your car to the mechanic and tell him to replace the engine, you don't care what's wrong or whether it needs doing? Somehow I don't think so.
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
August 5, 2011 at 5:01 am
Thanks ,
so how to recover a database from suspect mode?
what are the steps we have to take care ??
August 5, 2011 at 5:02 am
GilaMonster (8/4/2011)
It's caused when SQL either fails to complete a transaction rollback due to corruption or when it start but can't complete crash recovery, usually due to the same.Fix is restore from backup.
Repair is for those DBAs who are irresponsible enough not to have backups of their critical databases and hence have no other option but to hope and pray that the repair doesn't discard too much important data and that the repair actually succeeds (which it doesn't always)
i.e. it's a last resort
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
August 5, 2011 at 5:08 am
i faced same issue in previous . we have one week back backup for that database . As u mentioned fix the issue with restore with backup .
If i restore with one week back backup file surely we loose the data after that backup ??
So please advise
August 5, 2011 at 5:13 am
Sure, but having only a week old backup of (I assume) an important database is a stuff-up in and of itself. Surely if the data's important to you (or your users) you run backups a little more often than that, have transaction log backups, differential backups if necessary and can restore the the point of failure.
If you decide to run backups once a week, then you are deciding that up to a week's data can be lost in the case of a disaster. If that's not true, then you might need to reconsider that backup plan.
A DBA's first responsibility is to ensure that the database is recoverable, no matter what. If you're not doing that.....
Besides, do you know how much data the repair lost? Do you know what data the repair lost? Do you know how important the data was that the repair 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
August 5, 2011 at 5:42 am
Thanks Gila,
You are right. Data is very important.
The main reason why my database goes to suspect mode is server restarted 2 times . Paricular database goes to suspect mode .
If suppose we dont have any backups for that database what we have to do ??
By using the above commands how much data we will looose ?what are the chances of the issues ???
So when we will use those commands ,
August 5, 2011 at 5:53 am
Lavanyasri (8/5/2011)
Thanks Gila,You are right. Data is very important.
The main reason why my database goes to suspect mode is server restarted 2 times . Paricular database goes to suspect mode .
If suppose we dont have any backups for that database what we have to do ??
By using the above commands how much data we will looose ?what are the chances of the issues ???
So when we will use those commands ,
How much data loss depends on where the corruption is, there is no way to tell up front. I would try to figure out where the problem occurred and restore any backups. Typically the only times I have ever seen a Suspect database was when the transaction log drive array took a dump and was unrecoverable (drive cage failure). We had to restore the full backups and transaction logs up to the point of the failure, in my organization 45 minutes is the acceptable loss of data on most system, so our Tlog backups run every 45 minutes.
If you don't have any backups, I would try to see where the issue occurred that caused the issue, then go from there and follow the steps other people have outlined. I have never had to use those steps myself
There may be no issue at all with your data, but you will have to live with any data loss.
August 5, 2011 at 6:01 am
Lavanyasri (8/5/2011)
The main reason why my database goes to suspect mode is server restarted 2 times . Paricular database goes to suspect mode .
There's something wrong with your IO subsystem.
If suppose we dont have any backups for that database what we have to do ??
Firstly you go and update your resume, if you have no backups of an important database you are asking to be fired. Ensuring a database can be recovered no matter what is a DBA's primary responsibility. Having no backups means you've failed at that.
By using the above commands how much data we will looose ?
How long is a piece of string?
Absolutely no way to say, depends on the type and severity of the corruption. Can range from almost nothing to the entire database. That's why just repairing with no investigation, no analysis is such an irresponsible thing to do.
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
August 5, 2011 at 6:27 am
Thanks Gila,
Cool , First day of my previous office i faced this issue , but that is DEV environment. i requested them any backup are there . No answer .
Then i satrted to recover by using my quiries .
Thank you very much Gila...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply