DB in suspect mode

Question

DB in suspect mode

beejug1983

SSCertifiable

Points: 6491
More actions
June 15, 2010 at 8:24 am

#140748

What internally happens if db goes in suspect mode ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

SQL sets the database state to suspect (in sys.databases) and prevents anyone from accessing or querying the DB.

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

beejug1983 SSCertifiable Points: 6491 More actions · Answer 2

I meant exactly what happens that causes suspect mode to db?

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

many reasons, mainly :

1. Missing device causes database to be marked suspect.

2. Disk out of space and no space to expand log file.

3. If one or more database files are not available.

4.If the entire database is not available.

5.If one or more database files are corrupted.

6.If a database resource is being held by the operating system.

and could be many more reasons. I could not recollect.

SQL DBA.

Animal Magic SSChampion Points: 13964 More actions · Answer 4

Check your sql error logs. There should be some messages that relate to that database being marked as suspect. Dont do anything until you have done this

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

1. Missing device causes database to be marked suspect.

Won't cause suspect, can cause recovery_pending

2. Disk out of space and no space to expand log file.

Will not cause a database to go suspect. Read-only at worst.

3. If one or more database files are not available.
4.If the entire database is not available.

Won't cause suspect, can cause recovery_pending

5.If one or more database files are corrupted.

Will only result in the db going suspect if that corruption is encountered during a rollback/rollforward operation.

6.If a database resource is being held by the operating system.

Won't cause suspect, can cause recovery_pending

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 6

beejug1983 (6/15/2010)
I meant exactly what happens that causes suspect mode to db?

SQL will mark a database suspect if and only if corruption (data file or log file) is encountered during a transaction rollback or during restart-recovery when bringing a database online.

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

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 7

Thank you a lot.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris