Solved

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

Posted on 2013-06-28
22
716 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 27

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 27

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 27

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 27

Expert Comment

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

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

738 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