Solved

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

Posted on 2013-06-28
22
701 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
Comment Utility
The message is derived from a recovery, probably the owner of the machine has one...
0
 
LVL 2

Expert Comment

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

Expert Comment

by:gpizzuto
Comment Utility
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
 
LVL 8

Expert Comment

by:didnthaveaname
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
#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
Comment Utility
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
Comment Utility
#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
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 32

Author Comment

by:DrDamnit
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
#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
Comment Utility
Did you try offline/online'ing it like Zberteoc suggested ?
0
 
LVL 32

Author Comment

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

Expert Comment

by:Zberteoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now