Solved

MS SQL Database could not be recovered. Diagnose errors or restore backup?

Posted on 2013-06-28
22
710 Views
Last Modified: 2013-06-28
A program (Chiro8000) cannot be started, and fails with an error: "It appears SQL service is not running. Please ensure that it's running and try again."

The SQL service is, in fact running; however, there is an error in the event logs:

An error occurred during recovery, preventing the database 'PM_CHIRO_V12' (database ID 5) 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.

Open in new window


How do I diagnose errors in an MSSQL database? I would prefer not to have to restore from a backup since the owner of this machine probably does not have one.

SQL Server Version: MSSQL 2005.
0
Comment
Question by:DrDamnit
  • 7
  • 7
  • 4
  • +3
22 Comments
 
LVL 8

Expert Comment

by:gpizzuto
ID: 39285225
The message is derived from a recovery, probably the owner of the machine has one...
0
 
LVL 2

Expert Comment

by:nate0187
ID: 39285237
Did you check that the SQL service was running?  Can you be a little bit more informative?
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 39285259
The user that runs sqlserver service must have the rights to read and write inside the folder. Check the protection properties of the folder.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285266
What is the state of the database?

select name, state, state_desc from sys.databases where name = N'PM_CHIRO_V12';

Open in new window

0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285316
The MSSQL server is running, but it appears (from the error above) that this particular instance cannot load / read / deal with the database.

@didnthaveaname:
I am installing management studio now, and will run that query shortly...
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285346
Selecting the database in management studio, gives me this error:
Error Screenshot
then, it marks the DB as "Suspect"
Suspect = Kaiser Sose.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39285365
You can try:

1. Set the database offline and then bring back online
2. Restart SQL server
3. Run a DBCC CHECKDB  (http://msdn.microsoft.com/en-us/library/ms176064.aspx)
3. If above doeen't work then drop database and restore it
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285387
#1 doesn't appear to be an option. (This is MSSQL 2005 Express). GUI doesn't have a "Take Offline" function?

#2. This was my first instinct. It's been repeatedly restarted. No joy there.

#3. Will try this next.

#4. Drop DB and restore - client doesn't have a backup. But they do have Mozy. Is it possible to stop the SQL service, then restore the directory containing all the SQL files and have it work?
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285395
DBCC CHECKDB results:
DBCC results for 'master'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 693 rows in 6 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 101 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 112 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 693 rows in 7 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 101 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 112 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 15 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 7 rows in 1 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 1773 rows in 17 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 66 rows in 3 pages for object "sys.sysschobjs".
DBCC results for 'sys.syslogshippers'.
There are 0 rows in 0 pages for object "sys.syslogshippers".
DBCC results for 'sys.syscolpars'.
There are 558 rows in 10 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 28 rows in 1 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 1 rows in 1 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 5 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 1 rows in 1 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 134 rows in 2 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 252 rows in 1 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 5 rows in 1 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 210 rows in 36 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 15 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 144 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 107 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 16 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 1 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 6 rows in 1 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object "spt_fallback_db".
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object "spt_fallback_dev".
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object "spt_fallback_usg".
DBCC results for 'sys.queue_messages_1003150619'.
There are 0 rows in 0 pages for object "sys.queue_messages_1003150619".
DBCC results for 'sys.queue_messages_1035150733'.
There are 0 rows in 0 pages for object "sys.queue_messages_1035150733".
DBCC results for 'sys.queue_messages_1067150847'.
There are 0 rows in 0 pages for object "sys.queue_messages_1067150847".
DBCC results for 'MSreplication_options'.
There are 3 rows in 1 pages for object "MSreplication_options".
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object "spt_monitor".
DBCC results for 'spt_values'.
There are 2346 rows in 16 pages for object "spt_values".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC results for 'mssqlsystemresource'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 792 rows in 7 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 148 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 159 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 792 rows in 7 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 148 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 159 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 20 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2756 rows in 48 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 15804 rows in 289 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 2 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 3 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 348 rows in 6 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 506 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 3128 rows in 1184 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 766 rows in 5 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 1007 rows in 5 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 2 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 435 rows in 4 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 666 rows in 4 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 289 rows in 2 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 833 rows in 5 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 847 rows in 26 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syspalvalues'.
There are 384 rows in 3 pages for object "sys.syspalvalues".
DBCC results for 'sys.spt_server_info'.
There are 27 rows in 1 pages for object "sys.spt_server_info".
DBCC results for 'sys.spt_datatype_info'.
There are 66 rows in 1 pages for object "sys.spt_datatype_info".
DBCC results for 'sys.role_permissions'.
There are 169 rows in 2 pages for object "sys.role_permissions".
DBCC results for 'sys.sysbinpals'.
There are 45 rows in 1 pages for object "sys.sysbinpals".
DBCC results for 'sys.syscolrdb'.
There are 10715 rows in 166 pages for object "sys.syscolrdb".
DBCC results for 'sys.spt_provider_types'.
There are 27 rows in 1 pages for object "sys.spt_provider_types".
DBCC results for 'sys.syspalnames'.
There are 112 rows in 1 pages for object "sys.syspalnames".
DBCC results for 'sys.spt_permission_names'.
There are 5 rows in 1 pages for object "sys.spt_permission_names".
DBCC results for 'sys.sysobjrdb'.
There are 1773 rows in 24 pages for object "sys.sysobjrdb".
DBCC results for 'sys.spt_datatype_info_ext'.
There are 10 rows in 1 pages for object "sys.spt_datatype_info_ext".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Open in new window

0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285399
#4 Yes.  You would use a create database with the for attach clause.  Definitely restore the mdf/ndf/ldf's to a new location so you don't lose what you already have, though.

Create Database ... For Attach : http://msdn.microsoft.com/en-us/library/ms176061.aspx
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285413
I think you need to:

dbcc checkdb( PM_CHIRO_V12 ) with tablock;

Open in new window


If you don't specify a db, it defaults to your current context.

Edit: added tablock - will make checkdb run faster...
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285418
Ran:
dbcc checkdb( PM_CHIRO_V12 )

Open in new window


This time it gave me this:

Msg 926, Level 14, State 1, Line 1
Database 'PM_CHIRO_V12' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Open in new window

0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285432
Try this: http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

The Repairing a SUSPECT Database part is particularly what you're going to want.  Make sure you don't detach the db.  Not sure if you'll be able to, since I believe suspect databases are still attached, but I'd try copying the underlying data files first, just to be on the safe side.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39285434
To take offline you right click on the database node > Tasks > Take Offline

Same to bring online only you choose the opposite.

You can also check this:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f5463989-9a8e-4814-92de-1cde8f64ca49/how-to-bring-back-a-database-online-from-suspect-mode

If it doesn't work, which probably won't,  go with #4.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285437
Actually, on second thought, maybe you don't want to checkdb and allow large dataloss.  That's usually a last resort.  I just started looking in depth at the TSQL he was running.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285439
#4 just evaporated as an option. Mozy only backed up their profile. Not the contents of program files \ MSSQL \ etc\ \etc\ data....

Is there a way to repair this DB or change its state back to the way it was 24 hours ago?
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285449
Did you try offline/online'ing it like Zberteoc suggested ?
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39285454
That option doesn't appear to be an option:
No offline / online option
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39285461
Why is the server marked as stopped? I am referring to the red sign in the picture above. Is your server actually running?
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285473
Try:
alter database PM_CHIRO_V12 set offline;

Open in new window


While it's offline, you might as well copy the underlying datafiles (mdf/all ndf's/all ldf's) to have them somewhere else while you play around.

Then:
alter database PM_CHIRO_V12 set online;

Open in new window


Edit: Good eye, Zberteoc.  I didn't even notice that...
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39285516
It could be that it only needs refresh. He was able to run that DBCC.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39285695
Michael, it looks like you are in good hands, but here is another set of instructions for the repair of SUSPECT db. Notice the sp_resetstatus and DBCC checkdb in between the setting of emergency and single-user mode statements. Those are differences if I am not mistaken from the other instructions.

https://support.appzone.com/KB/a398/how-to-repair-a-suspect-database-in-mssql.aspx
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question