How to recover Database from Suspect mode

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » How to recover Database from Suspect mode

How to recover Database from Suspect mode

Rate Topic

Display Mode

Topic Options

Author

Message

trayalacheruvu

Posted Thursday, July 09, 2009 4:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 10:18 PM
Points: 25, Visits: 152

Hi ,

One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.

The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.

Please note other databases in the same server are working fine.

We tried the following options but with no success.

1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.

Since the database is in suspect status, we are neither able to export the data nor able to back up the database.

Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands,

scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

Post #750068

sanderstad

Posted Thursday, July 09, 2009 5:26 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:19 AM
Points: 382, Visits: 487

Maybe this website can help you with that: http://binaryworld.net/Main/CodeDetail.aspx?CodeId=3847

Post #750087

David Sorauer

Posted Thursday, July 09, 2009 8:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:27 PM
Points: 170, Visits: 553

In my experience...restore from backup!

I have never been able to recover a suspect database (unless the server only run out of log space)...

Post #750279

GilaMonster

Posted Thursday, July 09, 2009 8:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Recovery method depends on why the DB is suspect in the first place. There are a number of possible causes, with radically different solutions.
Please can you look through the error log and look for any messages that indicate why the DB has been marked suspect. Post those messages here.

Do you have a recent backup of this database?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #750298

karen.livingston 48906

Posted Wednesday, September 15, 2010 1:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 3:11 PM
Points: 1, Visits: 2

Hi,

Could someone tell me how to recover from a database with the status of 'Suspect' when the log file ran out of space? I'm running on SQL 2000.

Post #986613

« Prev Topic | Next Topic »

Permissions