Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Databases are under "Suspect" Mode
Add to briefcase

Databases are under "Suspect" Mode Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SQLCrazyCertified
Posted Tuesday, January 08, 2013 2:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 312, Visits: 1,871
All,

I have couple of the databases are under suspect mode.

What are some of the process you should follow under this circumstance. Please advise.

Thanks,
SueTons.
Post #1404454
george sibbald
Posted Tuesday, January 08, 2013 3:23 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 5,270, Visits: 11,213
firstly check the SQL errorlog for error messages - next steps depend on the errors.

---------------------------------------------------------------------
Post #1404464
SQLCrazyCertified
Posted Tuesday, January 08, 2013 3:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 312, Visits: 1,871
Thanks, I did that.

I ran dbcc chekcdb(dbname)....and looked at the error, I see an error 'An error occurred in the service broker manager, error:823, state 2.

I also see another error says 'The operating system returned error 21(the device is not ready.) to sql server during at offset 0x0000..........etc.

I know we can reset the status to recovery.....but I am more looking at what exactly the cause for this so, it won't happen again.

Thanks,
SueTons.
Post #1404468
SQLRNNR
Posted Tuesday, January 08, 2013 4:07 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:25 AM
Points: 18,754, Visits: 12,337
SQLCrazyCertified (1/8/2013)
Thanks, I did that.

I ran dbcc chekcdb(dbname)....and looked at the error, I see an error 'An error occurred in the service broker manager, error:823, state 2.

I also see another error says 'The operating system returned error 21(the device is not ready.) to sql server during at offset 0x0000..........etc.

I know we can reset the status to recovery.....but I am more looking at what exactly the cause for this so, it won't happen again.

Thanks,
SueTons.



The operating system returned error 21(the device is not ready.)

This message is an operating system error. It is more of a generic message letting you know there is an issue with the disk subsystem. It can be caused by a number of things such as:

LUNS not being available for the OS
the SAN was unplugged


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1404485
SQLCrazyCertified
Posted Tuesday, January 08, 2013 4:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 312, Visits: 1,871
Thanks Jason. Will look into it.

SueTons.
Post #1404491
GilaMonster
Posted Wednesday, January 09, 2013 2:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025
Post the exact errors from the SQL error log (all of them), then switch the DB into Emergency mode and run
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

and post those errors

You can't just reset the status on a suspect DB to normal. Suspect means damaged, has to be restored (prefered) or repaired (if there's no backup)

If it's just the error 21, then a restart of SQL might fix it, and then you need to identify why the disks are disappearing underneath SQL.

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 #1404618
SQLCrazyCertified
Posted Wednesday, January 09, 2013 5:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 312, Visits: 1,871
alexandert680@gmail.com1/9/2013)[/b]
Post the exact errors from the SQL error log (all of them), then switch the DB into Emergency mode and run
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

and post those errors

You can't just reset the status on a suspect DB to normal. Suspect means damaged, has to be restored (prefered) or repaired (if there's no backup)

If it's just the error 21, then a restart of SQL might fix it, and then you need to identify why the disks are disappearing underneath SQL.[/quote]

One of my colleague reboot the server and it solved the issue. Anyway, I had contacted the windows/storage team to investigate further.

Thanks for all your valuable inputs.

SueTons.
Post #1405083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.