March 22, 2010 at 9:11 am
Hi,
What are the possibilities for database going into Suspect mode???
Regards,
Saravanan
March 22, 2010 at 9:19 am
"Suspect" means the service can't access the database correctly.
Check the error logs. They'll usually tell you the exact problem.
- 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
March 22, 2010 at 9:34 am
Suspect means that the database may be transactionally or structurally inconsistent. This is the result of either a rollback failing (typically because it encounters a corrupt page) or the restart-recovery failing to complete (typically because it encounters a corrupt page or log record)
Because the rollback or roll-forward could not complete, the database can no longer be guaranteed consistent and must be marked suspect and taken offline.
If SQL couldn't access a file while bringing the DB online, it marks the database recovery-pending, not suspect.
There will always be information in the SQL error log saying why the database was marked suspect (or recovery-pending if that's the case)
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
March 22, 2010 at 10:07 am
Saravanan T (3/22/2010)
Hi,What are the possibilities for database going into Suspect mode???
There can be possibilities if some of the following are true. (there may be more reasons.. but the ones i have been through)
When you move a database across servers (test to prod) etc.. you need to check the database logical name and physical file path.
If you have changed the logical name and physical path and not re-attached the database, it will go into a suspect mode.
Even if you restore the master database, it might be the cause of a database going into the suspect mode if the SID's are invalid.
Maninder
www.dbanation.com
March 22, 2010 at 10:03 pm
My database dint go to Suspect mode. I just want to know the possibilities.Anyway thanks.
Regards,
Saravanan
March 23, 2010 at 1:55 am
There can be possibilities if some of the following are true. (there may be more reasons.. but the ones i have been through)
When you move a database across servers (test to prod) etc.. you need to check the database logical name and physical file path.
If you have changed the logical name and physical path and not re-attached the database, it will go into a suspect mode.
Even if you restore the master database, it might be the cause of a database going into the suspect mode if the SID's are invalid.
So should we do in each case..?
How to ge it in normal mode.?
March 23, 2010 at 1:56 am
Maninder S. (3/22/2010)
There can be possibilities if some of the following are true. (there may be more reasons.. but the ones i have been through)When you move a database across servers (test to prod) etc.. you need to check the database logical name and physical file path.
If you have changed the logical name and physical path and not re-attached the database, it will go into a suspect mode.
Even if you restore the master database, it might be the cause of a database going into the suspect mode if the SID's are invalid.
None of those will send a database suspect. Recovery_pending possibly, but not suspect. Suspect requires a rollback or roll-forward to start and fail
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
March 23, 2010 at 1:59 am
Joy Smith San (3/23/2010)
So should we do in each case..?How to ge it in normal mode.?
Fix the source of the problem. If the files aren't available, get them available, etc.
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
March 23, 2010 at 3:45 am
---Lines from books online--
A database can become suspect for several reasons. Possible causes include denial of access to a database resource by the operating system, and the unavailability or corruption of one or more database files.
March 24, 2010 at 6:43 am
I just had this problem yesterday. A disk array was reporting a Predictive Failure and the server restarted overnight. I could see the in the error logs that the recovery kept failing while it was in recovery mode and then it went into suspect mode. In our case, the server was end-of-life and the issue was most likely hardware-related.
March 25, 2010 at 7:05 am
So what you did after identifying the issue.? how you brought the server back to online.?
March 25, 2010 at 8:56 am
As it was end of life and the server was only a backup, we dropped the subscription and scrapped the server. However, if we would have needed it, we would have tried restoring from the previous night's backup. I do not know if this would work since we did not try it.
March 29, 2010 at 6:13 am
Follow following steps
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
March 29, 2010 at 8:50 am
chowdary.gb (3/29/2010)
EXEC sp_resetstatus 'yourDBname';ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
No! No! No!
That is the last resort for fixing suspect databases and may not be the appropriate solution depending on exactly why the database is suspect in the first place
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
March 29, 2010 at 10:18 pm
"REPAIR_ALLOW_DATA_LOSS" - I don't think this is first solution for the situation..... Perhaps it might be the last one.....
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply