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

Databases are under "Suspect" Mode Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 2:38 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
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.


Regards,
SQLisAwe5oMe.
Post #1404454
Posted Tuesday, January 8, 2013 3:23 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 5,888, Visits: 13,062
firstly check the SQL errorlog for error messages - next steps depend on the errors.

---------------------------------------------------------------------

Post #1404464
Posted Tuesday, January 8, 2013 3:29 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
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.


Regards,
SQLisAwe5oMe.
Post #1404468
Posted Tuesday, January 8, 2013 4:07 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1404485
Posted Tuesday, January 8, 2013 4:21 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
Thanks Jason. Will look into it.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1404491
Posted Wednesday, January 9, 2013 2:37 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
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
Posted Wednesday, January 9, 2013 5:23 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
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.


Regards,
SQLisAwe5oMe.
Post #1405083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse