Remove suspect databases

Posted on 2004-11-27
Last Modified: 2007-12-19
I have an urgent problem I need help with!!
Last night we lost power and our SQL server kicked out without shutting down properly.
When the server came back this morning, the distribution and my_dbase databases were both marked as 'Suspect'.  I cant delete the my_dbase and recreate it as it is being used for replication.  I can remove the replication from the server becuase the distribution database is suspect.  Ive tried using sp_resetstatus on both, but that doent help.
I dont have a backup of the distribution database, but I can recreate the replications if I have to.  i do have a backup of the my_dbase database, so that can be restored OK.
Please help me fix my replication!!!
Question by:maunded
    LVL 6

    Assisted Solution

    LVL 6

    Accepted Solution

    For some reason the above link didn't work when i clicked on it, so try:

    Use emergency mode (also called bypass mode) to recover data when SQL Server marks a database suspect. Occasionally something (hardware glitches, software glitches, gamma rays, or whatever) causes SQL Server's ordinarily robust automatic recovery to fail. Of course, recovery failure is never a problem because everyone always has an accurate, up-to-date backup handy. Uh- huh. Right. But what if (hypothetically) you don't have a good backup?

    Most of your data is probably still there on disk, but you can't get to it because SQL Server says the database is suspect. Setting the database status to emergency mode tells SQL Server to skip automatic recovery and lets you access the data. To get your data, use this script:

    Sp_configure "allow updates", 1

    Reconfigure with override


    Update sysdatabases set status = -32768 where name = "BadDbName"

    Sp_configure "allow updates", 0

    Reconfigure with override


    You might be able to use bulk copy program (bcp), simple SELECT commands, or DUMP TRANSACTION WITH NO_LOG to extract your data while the database is in emergency mode, but you won't be able to issue data modification commands. Get your data out as fast as you can before anything else can go wrong. Count your blessings if it's all there, because we can't guarantee that this technique will work in all cases. Call Microsoft if you're still stuck. The $150 service call is worth the money if Product Support can help you recover critical data. (See the Microsoft Knowledge Base article Q165918-- more information on this topic.)

    Why might SQL Server mark your database suspect? For starters, try querying on suspect in SQL Server Books Online (BOL) in the MS SQL Server 6.5 Group in the Programs Start Menu. You'll be surprised how many hits you get. Granted, some of them are for the verb suspect, but you'll have dozens of hits on suspect as an adjective. For example, you'll find stored procedures such as sp_resetstatus and sp_mark suspect that turn a database's suspect flag off and on, respectively. Marking a database suspect is often the easiest way to drop it (if you can't drop it using SQL Enterprise Manager's Manage Databases or Server Manager windows) because you can drop suspect databases. Use the command

    DBCC DBREPAIR(database_name, dropdb)

    (Remember, though, that you can never drop master, model, or tempdb databases, or a database that's open or participating in replication.)

    Also recognize that DBCC DBREPAIR is a command of last resort. Always try DROP DATABASE or sp_db remove first. DBCC DBREPAIR is for backward compatibility only.

    LVL 1

    Author Comment

    Sorry about that..totally forgot I had this problem
    To fix it, I had to delete the my_dbase then delete the distribution db, then restore my_dbase then recreate the replications.
    Hope this might help someone else...  

    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

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now