database is in suspect mode (sql server 2008 R2 version)
-
Hi,
One of our dev database is in suspect mode, not sure what went wrong.
Below are the details
DBCC CHECKDB (My1C_DevMS) WITH NO_INFOMSGS, ALL_ERRORMSGS
Msg 926, Level 14, State 1, Line 1
Database 'My1C_DevMS' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information
SQL Server Errorlogs:
Date10/5/2011 8:54:03 PM
LogSQL Server (Archive #2 - 10/6/2011 8:33:00 AM)
Sourcespid19s
Message
Operating system error 1117(The request could not be performed because of an I/O device error.) on file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\My1C_DevMS_1.ldf" during CheckLogBlockReadComplete.
Date10/5/2011 8:54:03 PM
LogSQL Server (Archive #2 - 10/6/2011 8:33:00 AM)
Sourcespid19s
Message
An error occurred during recovery, preventing the database 'My1C_DevMS' (database ID 17) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Please advice , how to proceed next?
Thnaks much!!
-
We have a sql server here that the data drive is an iscsi drive (it's a dev box that gets little use).
Sometimes there'll be a hick up between the server and that drive and the sql server sets the databases with files on that disk to suspect and logs the same errors. We also get those errors in this server when it is rebooted. The drive does not seem to be up to working condition before the SQL Server comes up and tries to recover the databases on that disk.
In our case, since it's a low importance SQL Server, a quick restart of the SQL Server service fixes things. But your database is in the system drive, so if the server is up, it cannot be because the disk is not online.
Have you checked the Event Viewer on that server yet? It may have more information about what's going on. Have your server guys look into that and make sure the disk is ok.
If it is a small database and it won't take long to restore it, I'd restore it to another sql server (or even the same as long as you restore it with a different name) to make sure the backup is good and then run DBCC on it to be double sure, before doing anything else.
-
laddu4700 (10/7/2011)
Hi,One of our dev database is in suspect mode, not sure what went wrong.
Below are the details
DBCC CHECKDB (My1C_DevMS) WITH NO_INFOMSGS, ALL_ERRORMSGS
Msg 926, Level 14, State 1, Line 1
Database 'My1C_DevMS' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information
SQL Server Errorlogs:
Date10/5/2011 8:54:03 PM
LogSQL Server (Archive #2 - 10/6/2011 8:33:00 AM)
Sourcespid19s
Message
Operating system error 1117(The request could not be performed because of an I/O device error.) on file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\My1C_DevMS_1.ldf" during CheckLogBlockReadComplete.
Date10/5/2011 8:54:03 PM
LogSQL Server (Archive #2 - 10/6/2011 8:33:00 AM)
Sourcespid19s
Message
An error occurred during recovery, preventing the database 'My1C_DevMS' (database ID 17) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Please advice , how to proceed next?
Thnaks much!!
The error gives you your answer. The only fix real fix for this is a restore from last known good backup. Your transaction log file is corrupt.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Agreed, restore from backup. If you have a good backup strategy, you'll only lose since the last log backup.
If there is no backup, it might be possible to repair, but there will almost certainly be data loss.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
laddu4700 (10/7/2011)
Hi,One of our dev database is in suspect mode, not sure what went wrong.
Below are the details
DBCC CHECKDB (My1C_DevMS) WITH NO_INFOMSGS, ALL_ERRORMSGS
Msg 926, Level 14, State 1, Line 1
Database 'My1C_DevMS' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information
SQL Server Errorlogs:
Date10/5/2011 8:54:03 PM
LogSQL Server (Archive #2 - 10/6/2011 8:33:00 AM)
Sourcespid19s
Message
Operating system error 1117(The request could not be performed because of an I/O device error.) on file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\My1C_DevMS_1.ldf" during CheckLogBlockReadComplete.
Date10/5/2011 8:54:03 PM
LogSQL Server (Archive #2 - 10/6/2011 8:33:00 AM)
Sourcespid19s
Message
An error occurred during recovery, preventing the database 'My1C_DevMS' (database ID 17) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Please advice , how to proceed next?
Thnaks much!!
Set the database to emergency mode and re run the DBCC CHECKDB. I t may be possible to repair the database if you have no backup but some data loss would definitely be encountered I'm sure.
ALTER DATABASE [MyDB] SET EMERGENCY-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
GilaMonster (10/8/2011)
Agreed, restore from backup. If you have a good backup strategy, you'll only lose since the last log backup.If there is no backup, it might be possible to repair, but there will almost certainly be data loss.
How would you repair? Does CHECKDB do the error log as well as the data files?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
CHECKDB will rebuild the transaction log too, complete description is here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Thanks Perry. I hadn't seen that blog by Paul. Fortunately I've never needed to recover from a corrupt transaction log.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
ALTER DATABASE dbname SET EMERGENCY;
ALTER DATABASE dbname SET SINGLE_USER
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
-
zoushuangping (10/9/2011)
ALTER DATABASE dbname SET EMERGENCY;ALTER DATABASE dbname SET SINGLE_USER
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
So you're advising that he's best off losing some unknown amount of data?
Repair is a last resort. Emphasis 'last'. It's not the first thing tried.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Put the database in emergency mode and run dbcc checkdb with message output first to ascertain exactly what the extent of the damage is. As Gail pointed out, the repair option above is a last resort.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Could you please let us know how did you solved the problem? Did you restore a backup or did you try to recover the database?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply