Solved

HELP! SUSPECT Database in MS Sql 2005..

Posted on 2010-08-13
28
423 Views
Last Modified: 2012-05-10
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
Comment
Question by:engineroom
  • 11
  • 8
  • 4
  • +2
28 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 33430251
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
 
LVL 3

Author Comment

by:engineroom
ID: 33430404
Coudn't Detach... ALTER DATABASE statement failed.
0
 
LVL 3

Author Comment

by:engineroom
ID: 33430506
Wait.. new error...


Cannot Detach a suspect database. It must be repaired or dropped.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 33430577
Then see the SQL Log file for errors.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33430684
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33430687
Also, review disk space, make sure  you just have not run out of disk where that db is.
0
 
LVL 3

Author Comment

by:engineroom
ID: 33430724
It won't let me run the query against the database. What do i do now? I do have free space.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 33430744
To run DBCC, connect to the MASTER (or any other) DB. Does it work then?
0
 
LVL 3

Author Comment

by:engineroom
ID: 33430844
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''bgclubta.org''.

DBCC CHECKDB bgclubta.org
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 33430929
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
 
LVL 3

Author Comment

by:engineroom
ID: 33431671
DBCC CHECKDB "bgclubta.org"


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bgclubta.org'.
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 33431869
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
 
LVL 3

Author Comment

by:engineroom
ID: 33432100
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
 
LVL 7

Expert Comment

by:Cboudroz
ID: 33432242
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:engineroom
ID: 33432339
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
 
LVL 7

Expert Comment

by:Cboudroz
ID: 33432497
it always more easy to understand the print if you do it line by line.
0
 
LVL 3

Author Comment

by:engineroom
ID: 33432841
i'm confused. Sorry. what code do i run?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33432879
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
 
LVL 7

Expert Comment

by:Cboudroz
ID: 33432924
Like Scott said try this first:

USE master;
GO
EXEC sp_resetstatus 'CopyOfAdventureWorks2008';
GO


0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33432963
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33432978
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
 
LVL 3

Author Comment

by:engineroom
ID: 33433390
K. Restarted SQL Server.. It now says that it's (In Recover)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33433545
DID YOU CHECK THE ERROR LOG FOR THE MSG?

WHAT WAS IT?
0
 
LVL 3

Author Comment

by:engineroom
ID: 33433652
Error Log attached.
ERRORLOG
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33434039
Were you able to make a backup of the db?

Or did SQL not allow that either?
0
 
LVL 3

Author Comment

by:engineroom
ID: 33435045
no. unfortunately no backup.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 33435143
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33447126
Yep, that makes sense, esp. since SQL cannot currently find any errors on the db itself.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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