[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

HELP! SUSPECT Database in MS Sql 2005..

Title pretty much says it all. Woke up this morning to find a SUSPECT db. how do i get it back? Thanks all

er.
0
engineroom
Asked:
engineroom
  • 11
  • 8
  • 4
  • +2
1 Solution
 
QlemoDeveloperCommented:
Detach and reattach it, or put it "online" again. Both can be done by using the Managment Studio. If that does not help, you need to have a look into the SQL Log (again in Managment Studio) to see the reason, which you should post here so we can help with the next steps to perform.
0
 
engineroomAuthor Commented:
Coudn't Detach... ALTER DATABASE statement failed.
0
 
engineroomAuthor Commented:
Wait.. new error...


Cannot Detach a suspect database. It must be repaired or dropped.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
QlemoDeveloperCommented:
Then see the SQL Log file for errors.
0
 
Scott PletcherSenior DBACommented:
Whew.  NEVER detach a suspected db, it will NOT reattach successfully.

Check the SQL error log.

If a backup will work, make a backup of the db (so that, worst case, you can always get back to your original

Run a DBCC CHECKDB on the db; do NOT specify fix errors (yet), just run the check, see what's wrong.
0
 
Scott PletcherSenior DBACommented:
Also, review disk space, make sure  you just have not run out of disk where that db is.
0
 
engineroomAuthor Commented:
It won't let me run the query against the database. What do i do now? I do have free space.
0
 
QlemoDeveloperCommented:
To run DBCC, connect to the MASTER (or any other) DB. Does it work then?
0
 
engineroomAuthor Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''bgclubta.org''.

DBCC CHECKDB bgclubta.org
0
 
QlemoDeveloperCommented:
Since "." has a special meaning (separator between name parts), you need to enclose the database name either in [] or "", so
dbcc checkdb "bgclubta.org"
should run fine.
0
 
engineroomAuthor Commented:
DBCC CHECKDB "bgclubta.org"


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bgclubta.org'.
0
 
CboudrozCommented:
DBCC checkdb ([bgclubta.org])


DBCC CHECKDB 
[
    [ (database_name | database_id | 0
        [ , NOINDEX 
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH 
        {
            [ ALL_ERRORMSGS ]
            [ , EXTENDED_LOGICAL_CHECKS ] 
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]
        }
    ]
]

Open in new window

0
 
engineroomAuthor Commented:
here's the results



DBCC results for 'bgclubta.org'.
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 1173 rows in 9 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 159 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 197 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 1173 rows in 10 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 159 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 197 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 30 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 219 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 329 rows in 7 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1845 rows in 31 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 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 356 rows in 7 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 471 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 495 rows in 87 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 27 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 192 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 3103 rows in 24 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 0 rows in 0 pages for object "sys.sysguidrefs".
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 0 rows in 0 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 'order_items'.
There are 0 rows in 0 pages for object "order_items".
DBCC results for 'page_rating_summary'.
There are 0 rows in 0 pages for object "page_rating_summary".
DBCC results for 'coupons'.
There are 0 rows in 0 pages for object "coupons".
DBCC results for 'page_rating_voters'.
There are 0 rows in 0 pages for object "page_rating_voters".
DBCC results for 'taxes'.
There are 0 rows in 0 pages for object "taxes".
DBCC results for 'page_ratings'.
There are 0 rows in 0 pages for object "page_ratings".
DBCC results for 'custom_listings'.
There are 0 rows in 0 pages for object "custom_listings".
DBCC results for 'page_modules'.
There are 8 rows in 1 pages for object "page_modules".
DBCC results for 'aspnet_WebEvent_Events'.
There are 0 rows in 0 pages for object "aspnet_WebEvent_Events".
DBCC results for 'custom_listing_items'.
There are 0 rows in 0 pages for object "custom_listing_items".
DBCC results for 'placeholders'.
There are 0 rows in 0 pages for object "placeholders".
DBCC results for 'calendar'.
There are 4 rows in 1 pages for object "calendar".
DBCC results for 'modules'.
There are 31 rows in 1 pages for object "modules".
DBCC results for 'membership_add'.
There are 0 rows in 0 pages for object "membership_add".
DBCC results for 'pages'.
There are 267 rows in 32 pages for object "pages".
DBCC results for 'shipping_info'.
There are 0 rows in 0 pages for object "shipping_info".
DBCC results for 'templates'.
There are 32 rows in 1 pages for object "templates".
DBCC results for 'product_options_definition'.
There are 0 rows in 0 pages for object "product_options_definition".
DBCC results for 'product_options_map'.
There are 0 rows in 0 pages for object "product_options_map".
DBCC results for 'tracking_site_daily'.
There are 13 rows in 1 pages for object "tracking_site_daily".
DBCC results for 'tracking_page_daily'.
There are 245 rows in 1 pages for object "tracking_page_daily".
DBCC results for 'page_comments'.
There are 0 rows in 0 pages for object "page_comments".
DBCC results for 'aspnet_Applications'.
There are 1 rows in 1 pages for object "aspnet_Applications".
DBCC results for 'aspnet_Users'.
There are 8 rows in 1 pages for object "aspnet_Users".
DBCC results for 'aspnet_SchemaVersions'.
There are 6 rows in 1 pages for object "aspnet_SchemaVersions".
DBCC results for 'poll_answers'.
There are 0 rows in 0 pages for object "poll_answers".
DBCC results for 'polls'.
There are 0 rows in 0 pages for object "polls".
DBCC results for 'poll_voters'.
There are 0 rows in 0 pages for object "poll_voters".
DBCC results for 'aspnet_Membership'.
There are 2 rows in 1 pages for object "aspnet_Membership".
DBCC results for 'locales'.
There are 1 rows in 1 pages for object "locales".
DBCC results for 'newsletters'.
There are 0 rows in 0 pages for object "newsletters".
DBCC results for 'newsletters_receipients'.
There are 0 rows in 0 pages for object "newsletters_receipients".
DBCC results for 'shipping_cost'.
There are 0 rows in 0 pages for object "shipping_cost".
DBCC results for 'country_state_lookup'.
There are 55 rows in 1 pages for object "country_state_lookup".
DBCC results for 'product_types'.
There are 0 rows in 0 pages for object "product_types".
DBCC results for 'product_property_definition'.
There are 0 rows in 0 pages for object "product_property_definition".
DBCC results for 'aspnet_Profile'.
There are 8 rows in 7 pages for object "aspnet_Profile".
DBCC results for 'product_property_values'.
There are 0 rows in 0 pages for object "product_property_values".
DBCC results for 'form_field_definitions'.
There are 8 rows in 1 pages for object "form_field_definitions".
DBCC results for 'form_field_values'.
There are 0 rows in 0 pages for object "form_field_values".
DBCC results for 'form_data'.
There are 8 rows in 1 pages for object "form_data".
DBCC results for 'aspnet_Roles'.
There are 9 rows in 1 pages for object "aspnet_Roles".
DBCC results for 'form_settings'.
There are 1 rows in 1 pages for object "form_settings".
DBCC results for 'form_data_impressions'.
There are 1 rows in 1 pages for object "form_data_impressions".
DBCC results for 'aspnet_UsersInRoles'.
There are 2 rows in 1 pages for object "aspnet_UsersInRoles".
DBCC results for 'newsletters_categories'.
There are 1 rows in 1 pages for object "newsletters_categories".
DBCC results for 'newsletters_subscribers'.
There are 0 rows in 0 pages for object "newsletters_subscribers".
DBCC results for 'newsletters_map'.
There are 0 rows in 0 pages for object "newsletters_map".
DBCC results for 'newsletters_settings'.
There are 1 rows in 1 pages for object "newsletters_settings".
DBCC results for 'discussion'.
There are 0 rows in 0 pages for object "discussion".
DBCC results for 'discussion_attachments'.
There are 0 rows in 0 pages for object "discussion_attachments".
DBCC results for 'topic_subscribers'.
There are 0 rows in 0 pages for object "topic_subscribers".
DBCC results for 'registration_settings'.
There are 1 rows in 1 pages for object "registration_settings".
DBCC results for 'aspnet_Paths'.
There are 0 rows in 0 pages for object "aspnet_Paths".
DBCC results for 'listing_categories'.
There are 0 rows in 0 pages for object "listing_categories".
DBCC results for 'listing_category_map'.
There are 0 rows in 0 pages for object "listing_category_map".
DBCC results for 'aspnet_PersonalizationAllUsers'.
There are 0 rows in 0 pages for object "aspnet_PersonalizationAllUsers".
DBCC results for 'guest_book'.
There are 0 rows in 0 pages for object "guest_book".
DBCC results for 'page_module_sorting'.
There are 0 rows in 0 pages for object "page_module_sorting".
DBCC results for 'aspnet_PersonalizationPerUser'.
There are 0 rows in 0 pages for object "aspnet_PersonalizationPerUser".
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".
DBCC results for 'content_templates'.
There are 7 rows in 1 pages for object "content_templates".
DBCC results for 'config_shop'.
There are 1 rows in 1 pages for object "config_shop".
DBCC results for 'listing_templates'.
There are 21 rows in 1 pages for object "listing_templates".
DBCC results for 'orders'.
There are 0 rows in 0 pages for object "orders".
DBCC results for 'channels'.
There are 1 rows in 1 pages for object "channels".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'bgclubta.org'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Open in new window

0
 
CboudrozCommented:
0
 
engineroomAuthor Commented:
With this... do i just copy and paste into query analyzer or do i do it line by line?

USE master;
GO
ALTER DATABASE CopyOfAdventureWorks2008 SET EMERGENCY
GO
ALTER DATABASE CopyOfAdventureWorks2008 SET SINGLE_USER
GO
DBCC CHECKDB (CopyOfAdventureWorks2008, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
 
USE CopyOfAdventureWorks2008;
 
DBCC CHECKDB WITH NO_INFOMSGS;
GO
0
 
CboudrozCommented:
it always more easy to understand the print if you do it line by line.
0
 
engineroomAuthor Commented:
i'm confused. Sorry. what code do i run?
0
 
Scott PletcherSenior DBACommented:
Do not use WITH DATA LOSS yet.

DBCC CHECKDB was good news -- there really doesn't appear to be anything damaged.

So first try "zapping" to good status and see if the db functions normally.
0
 
CboudrozCommented:
Like Scott said try this first:

USE master;
GO
EXEC sp_resetstatus 'CopyOfAdventureWorks2008';
GO


0
 
Scott PletcherSenior DBACommented:
Rats, never mind, SQL 2005 won't allow the zap.

You have to use the approved method, using:
EXEC sp_resetstatus 'databasename'

However, FIRST you MUST look in the SQL error log and show us the msg it gives about why the db became SUSPECT.

Because it looks like the db is good now based on the CHECKDB.

The only thing with sp_resetstatus is that Books Online says to stop and start SQL after using it.  That can very problematic during the day!!!  Not exactly a good way to solve this MS!!!!!
0
 
Scott PletcherSenior DBACommented:
I've had dbs get suspect and have just been able to "zap" or reset them to good status and everything was ok, because it was a temporary situation.

Looks like you may have this here because the CHECKDB showed 0 errors, which is of course what you want :-) .
0
 
engineroomAuthor Commented:
K. Restarted SQL Server.. It now says that it's (In Recover)
0
 
Scott PletcherSenior DBACommented:
DID YOU CHECK THE ERROR LOG FOR THE MSG?

WHAT WAS IT?
0
 
engineroomAuthor Commented:
Error Log attached.
ERRORLOG
0
 
Scott PletcherSenior DBACommented:
Were you able to make a backup of the db?

Or did SQL not allow that either?
0
 
engineroomAuthor Commented:
no. unfortunately no backup.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The first thing I would look to do is to upgrade that 2005 vanilla to at least SP3.  You have 9.00.1399 which is RTM of 2005 and that could be one of the problems.  You have an Access Violation  (AV for short) in the log.  This indicates that there is a problem in the engine.

But with SP0 on the engine, I would put SP3 on whenever you can have some down time, then see if it will recover that database correctly.
0
 
Scott PletcherSenior DBACommented:
Yep, that makes sense, esp. since SQL cannot currently find any errors on the db itself.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 8
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now