how do you fix databases that are in suspect mode.

Posted on 2009-04-15
Last Modified: 2012-05-06
We have a SQL server 2000 database that is in suspect mode. how do I recover from this.

Question by:Steve Samson
    LVL 8

    Accepted Solution

    LVL 20

    Expert Comment

    by:Marten Rune
    Look at the posting:

    When did it get suspect, after a restart? Do you have a antivirus program on the server, it might hog the physical files, and hence the server can not access them. Stop and start the SQL Services to find out.

    Check the log for errors, is it possible that your harddrive is failing. If so you better find out fast.

    Can you provide more info on when it got suspected, if you tried a SQL server restart. Is it the only database on the sql. What SP is your SQL running, on what OS?

    LVL 7

    Expert Comment

    by:Hemantgiri S. Goswami
    I would recommend to follow the instruction given here to recover the suspected database
    LVL 20

    Expert Comment

    by:Marten Rune
    Good article ghemant

    For the author ccutler:
    two things to emphasize
    1. If you need to use the 'repair_allow_data_loss' switch with DBCC. Maybe it's a better approach (assuming the db is in full recovery mode) to  make a tail log backup, and then simply drop and restore the database the normal way. If it's not in full recovery mode but in simple, check your SLA. You'll most likely find that a data loss of transactions up to 24h is acceptable (maybe the application can rerun those by design on appserver or clients). It should be evaluated if it's better to know that all the data is correct, but transactions for this day needs to be done all over.
    2. If DBCC finds that your database files has been damaged, check the log for errors regarding to disk failure, typically 823, 824 and 825 (825 is a warning, the other are failures). These indicate that there is something wrong with the I/O subsystem. You need to find out why corruption occurred, it will most likely occur again so preventive actions needs to be taken.


    Author Closing Comment

    by:Steve Samson
    Worked like a charm.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    734 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