Databse suspect condition

Question

Databse suspect condition

SQL Galaxy

One Orange Chip

Points: 29472
More actions
December 30, 2008 at 11:28 pm

#103652

Hi,
Which situation database comes to suspect condition?
Resolve way the problem – restore most recent backup from database back file.
can anybody suggestion me, is there any alternative way how do resolve the database suspect condition?
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

ananda.murugesan (12/30/2008)
can anybody suggestion me, is there any alternative way how do resolve the database suspect condition?

It completely depends on what caused the suspect condition in the first place. The error log usually has details on why a DB has been marked suspect.

Is this a theoretical question?

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Hi Gail,

Thanks for your reply..

error log we can found that what exact problem for database suspet mode.

yes..Is this a theoretical question?

could you tell me, any other alternative ways to solve this problem. not in restore.

thanks

ananda

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Some steps:

1.) Reset the status from "Suspect':

EXEC sp_resetstatus 'myDatabaseName'

2.) Set the database property "allow updates':

exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE

3.) Set the database to "Emergency Mode":

UPDATE master..sysdatabases SET status=-32768 WHERE

name='myDatabseName'

4.) Rebuild the database log file:

dbcc rebuild_log ('dblogfilename','C:\Program Files\Microsoft SQL

Server\MSSQL\Data\ myDatabaseName2_log.LDF')

MJ

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

ananda.murugesan (12/31/2008)
Hi Gail,
error log we can found that what exact problem for database suspet mode.

Sorry, I don't understand what you're saying

could you tell me, any other alternative ways to solve this problem. not in restore.

That depends entirely on why the database is suspect in the first place. Without knowing the cause, there is absolutely no way to make a recommendation on fixing it.

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 5

MANU (12/31/2008)
4.) Rebuild the database log file:
dbcc rebuild_log ('dblogfilename','C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ myDatabaseName2_log.LDF')

That should be the absolute last resort when all else has failed, not the first thing to try. Rebuilding the log can cause logical and physical inconsistencies in a database. That is, it can cause corruption and incorrect data.

Read this carefully, before trying the log rebuild - http://www.sqlskills.com/BLOGS/PAUL/post/Corruption-Last-resorts-that-people-try-first.aspx

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

SQL Galaxy

One Orange Chip

Points: 29472

January 1, 2009 at 2:16 am

#919913

hi manu,

thanks for your valuable reply..

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

Try bringing in database online and run DBCC CHECKDB. If it allows you to recover the database by not making use of any data loss option then its okie to proceed. If still no help then put database in emergency mode, pull the data out to new database.

As Gail said rebuild log is a dangerous option from consistency point of view so stay away from it. My suggestion was just to make you aware of the possible steps.

Thanks Gail.

MJ

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 8

In situations like this can I take the database to offline mode then take it to online mode?

"-=Still Learning=-"

Lester Policarpio

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

Lester Policarpio (1/4/2009)
In situations like this can I take the database to offline mode then take it to online mode?

You can. It's not likely, by itself, to resolve anything though.

SQL would have had a reason to mark the DB suspect. It's necessary to find and resolve the root cause before trying to remove the suspect flag. If the root cause is unresolved, the DB will just go straight back to 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

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