Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to recover Database from Suspect mode Expand / Collapse
Author
Message
Posted Thursday, July 9, 2009 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 26, 2014 11:10 PM
Points: 26, Visits: 160
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
Posted Thursday, July 9, 2009 5:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:34 AM
Points: 425, Visits: 549
Maybe this website can help you with that: http://binaryworld.net/Main/CodeDetail.aspx?CodeId=3847

Post #750087
Posted Thursday, July 9, 2009 8:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, July 9, 2009 8:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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
Posted Wednesday, September 15, 2010 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, May 22, 2014 4:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:47 PM
Points: 22, Visits: 37
Enter this script in new query window od SQL Server Management Studio

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




SSMS Expert
Post #1573501
Posted Thursday, May 22, 2014 4:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:47 PM
Points: 22, Visits: 37


SSMS Expert
Post #1573504
Posted Thursday, May 22, 2014 5:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Please note: 4 year old thread, and repair allow data loss should never be the first suggestion and should never be suggested without an explanation of the effects.


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 #1573509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse