Database in Suspect Mode

Question

Post reply

Database in Suspect Mode

Saravanan T

SSCrazy

Points: 2506
More actions
March 22, 2010 at 9:11 am

#156807

Hi,
What are the possibilities for database going into Suspect mode???
Regards,
Saravanan

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MannySingh SSChampion Points: 12465 More actions · Answer 3

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

Saravanan T SSCrazy Points: 2506 More actions · Answer 4

My database dint go to Suspect mode. I just want to know the possibilities.Anyway thanks.

Regards,
Saravanan

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

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.?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vidya_pande SSCertifiable Points: 5275 More actions · Answer 8

---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.

jatkin SSC Veteran Points: 206 More actions · Answer 9

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.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

So what you did after identifying the issue.? how you brought the server back to online.?

jatkin SSC Veteran Points: 206 More actions · Answer 11

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.

chowdary.gb Grasshopper Points: 24 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 14

"REPAIR_ALLOW_DATA_LOSS" - I don't think this is first solution for the situation..... Perhaps it might be the last one.....