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 12345»»»

A guide to recover a database out from Suspect mode Expand / Collapse
Author
Message
Posted Saturday, February 06, 2010 10:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:20 PM
Points: 194, Visits: 334
Comments posted to this topic are about the item A guide to recover a database out from Suspect mode


CDBA
Post #861264
Posted Sunday, February 07, 2010 11:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:24 AM
Points: 166, Visits: 1,048
Caution! Below steps will cause you to loose data...
It's "lose data."

Nice article, thanks. I'll remember RESTORE DATABASE WITH RECOVERY
Post #861442
Posted Monday, February 08, 2010 12:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
very helpful article. thx
Post #861452
Posted Monday, February 08, 2010 1:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 08, 2010 6:03 PM
Points: 13, Visits: 19
Good article - To the Point.

I had a problem of SUSPECTED Database few years back (SQL2000). I updated the status in sysdatabases, and the problem never reoccured.

Thanks

B.K.Prasad
Post #861476
Posted Monday, February 08, 2010 2:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 02, 2012 12:54 AM
Points: 1, Visits: 66
Well thats a really helping things to bring the Database Online. But before going to perform the thrid step I would like to go for DBCC DBRECOVER(DBNAME) command. Also after setting the Database in emergency do not forget to execute the DBCC Checkdb command, this will give what are the objects or transcations having the problem. Then in the end go for DBCC CHECKDB with allowloss option.
Post #861488
Posted Monday, February 08, 2010 2:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Very helpful arcticle..thanks a lot. Wish I should not come across this situation.
Post #861497
Posted Monday, February 08, 2010 5:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
Paul S. Randal is the definitive source on Database Corruption and recovering corrupt databases. Check him out at http://www.sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx He wrote DBCC CHECKDB for Mircosoft. A lot of things in this article and a whole lot more is in his site....

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #861562
Posted Monday, February 08, 2010 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:52 AM
Points: 2,919, Visits: 2,511
Another possible issue/solution, probably under possibility 1:

I get a lot of SAN detatch errors from a certain iSCSI SAN. The SAN Drives will drop, and reattach. Fortunately, the Windows OS sees the drives as being back but once SQL Server looses the connection, there is no reconnect. These will show up as suspect databases (multiple). Solution so far (and I have been lucky) is simply restart the SQL Service. Usually shows up as a 9002 error in the error log.


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #861612
Posted Monday, February 08, 2010 7:08 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 @ 11:52 AM
Points: 41,530, Visits: 34,446
Possibility 1: Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file. The solution is to place the missing data/log file in proper location. The SQL Server Error Log error message will give you the exact name and path of the missing file. Once you place the file execute below command to bring your database online with no data loss.

RESTORE DATABASE WITH RECOVERY

Possibility 2: SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool (like Antivirus), which puts an exclusive lock on the data/log file. To resolve it, use process explorer and kill the file handler which placed lock on the file. You may want to involve your System Admins to get this step executed. Then execute below command and you will have your database online with no data loss:

RESTORE DATABASE WITH RECOVERY


Sorry, but I am going to completely, 100% disagree with you.

RESTORE DATABASE <DB Name> WITH RECOVERY is what you run if your database is in a RESTORING state because an earlier backup was restored with NORECOVERY. It is not used to bring a database out of the suspect state.

I did a quick test. Created a DB, shut SQL down, renamed the data file and restarted SQL. The DB came up in the RECOVERY PENDING state (not suspect). I then followed your instructions, renamed the file correctly and ran the RESTORE DATBASE statement.

RESTORE DATABASE SuspectDB WITH RECOVERY

Msg 3148, Level 16, State 3, Line 1
This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


The correct solution, when a file has been moved or was locked by some other app when SQL started, is to rectify the root cause first (put the file back, remove whatever was locking it), then take the DB offline and bring it back online (or restart the SQL Service). That will allow SQL to open the DB again, this time find all the files and run the restart_recovery again this successfully.

This will work if the DB state is RECOVERY PENDING (meaning that restart-recovery has not started due to missing or inaccessible files). If the DB state is truly suspect, it means there's a problem in the data file or log file, the restart recovery started (so all files are accessible) but could not complete. That's a lot more severe than just a missing file and usually indicates corruption of the data/log that was encountered during the restart-recovery.

One additional note on Emergency mode repair. It's a last resort. If it fails, there's nothing else that can be done.



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 #861637
Posted Monday, February 08, 2010 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
I'm a little surprised that you're not recommending running CHECKDB on the database in question. Yes, it's marked as suspect, and yes, you might find a cause in the error log, but then again, you might not. Running a database consistency check will frequently identify the root cause. It might be something recoverable through other means than running repair with data loss, which is a very dangerous thing to do to a production system.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #861649
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse