SQL SERVER 2008 Database Suspect Mode Reasons

Question

SQL SERVER 2008 Database Suspect Mode Reasons

prashant.akerkar

SSC Enthusiast

Points: 157
More actions
June 10, 2010 at 11:38 pm

#222642

1.
I want to know all possible reasons for Particluar Database/s within SQL SERVER 2008 ENGINE going in suspect mode.
2. When the database goes into suspect mode will there be transaction commit ?
i.e I executed insert records through Stored Procedure/s from my web application when the database was in suspect mode.
1. whether records will be inserted in the application database ?
2. whether records inserted in the application database can be corrupted data ?
3. whether records will not be inserted and my web application will throw "Could not connect to Database" error ?
Thanks & Regards,
Prashant S Akerkar

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

A database will go suspect is SQL cannot complete a rollback or rollforward operation, typically due to encountering log or data corruption during the rollback or rollforward.

When a database is suspect, it cannot be opened and any queries issued against it will 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

prashant.akerkar SSC Enthusiast Points: 157 More actions · Answer 2

Thanks for the Reply.

As u mention when the Database is in suspect mode there will be no rollback / rollforward.

Two more scenarios :

1. When records are inserted (it could be bulk inserts through Stored Procedure) in Application Database from application web form, if power failure on the Database Server Machine occurs during at that time , will there be chances of data corruption for those specific database records.

2. When records are inserted (it could be bulk inserts through Stored Procedure) in Application Database from application web form, if someone stops the SQL SERVER Database Engine Service, will there be chances of data corruption for those specific database records.

Thanks & Regards,

Prashant S Akerkar

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

prashant.akerkar (6/11/2010)
As u mention when the Database is in suspect mode there will be no rollback / rollforward.

No, I said that a failed rollback/rollforward will result in a suspect database