Post reply

SUSPECT DB

  • March 14, 2008 at 6:52 pm

    #188045

    HI

    WE HAVE TWO DATABASES AT OUR WORK PLACE. BOTH ARE SQL 2000. ONE OF THEM IS SHOWING SUSPECT IN SYSTEM TRAY. CAN ANY OF YOU TELL ME HOW TO RECOVER THE DATABASE

    THANKS IN ADVANCE.

  • March 15, 2008 at 7:35 am

    #789986

    Please don't type in full caps. It's the online equivalent of shouting.

    Suspect in system tray? Not quite sure I understand.

    If you open enterprise manager and connect to the server in question, do you see the databases? Can you access them?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 15, 2008 at 9:18 am

    #790001

    Please also look in the error logs and there should be some clue about why they are suspect.

  • March 15, 2008 at 7:42 pm

    #790096

    sorry for typing in caps. the system tray icon has become grau in color, in enterprise manager if i click on plus next to database it is not expanding. the lodf file is corupted i think.

  • March 16, 2008 at 6:51 am

    #790127

    If you expand out the server in enterprise manager, can you see the system databases? Can you see the user databases? If you can see the user database, is there the word (suspect) next to the db name?

    Can you connect to the server with query analyser? If so, run the following please

    SELECT name, state from master.sys.databases

    What's the state of the database in question?

    Are there any entries in the sql error log concerning that database? Anything in the windows event log?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 17, 2008 at 4:14 am

    #790256

    Please run the sp_restetstatus system stored procedure.otherwise you can recycle the SQL services.If still this is the case,follow the below things.

    place your database in to emergency mode by using this

    __________________________________________________

    sp_configure 'allow updates', 1

    RECONFIGURE WITH OVERRIDE

    GO

    UPDATE master..sysdatabases set status = -32768 WHERE name = 'pubs'

    GO

    SP_CONFIGURE 'allow updates', 0

    RECONFIGURE WITH OVERRIDE

    After running the command, the database appears in Enterprise Manager in Read-Only\Offline\Emergency Mode. While the database is in this mode, you can only read from it. If you try to update any values, you receive the following error:

    Server: Msg 3908, Level 16, State 1, Line 1

    Could not run BEGIN TRANSACTION in database 'pubs'

    because the database is in bypass recovery mode.

    The statement has been terminated.

  • March 17, 2008 at 5:44 am

    #790274

    Ack. That should be the very last resort when all else fails, not the first thing to try.

    The system tables should not be updated. Doing so can have nasty consequences.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 18, 2008 at 6:19 am

    #790757

    I find that taking the database offline and then bringing it back online typically works. Or is that not the preferred way of doing it?

  • March 18, 2008 at 6:31 am

    #790761

    MANJUG05 (3/14/2008)

    HI

    WE HAVE TWO DATABASES AT OUR WORK PLACE. BOTH ARE SQL 2000. ONE OF THEM IS SHOWING SUSPECT IN SYSTEM TRAY. CAN ANY OF YOU TELL ME HOW TO RECOVER THE DATABASE

    THANKS IN ADVANCE.

    Hey,

    As already commented, you will need to find out why db has been marked suspect. You may want to check if disk space was exhaused. Also check for any unexpected power issues.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • March 23, 2008 at 10:42 pm

    #792912

    hi all

    i googled on how to recover a suspect db.

    i found the most viable thing to do is taking the database offline, dertach the db, see whether ldf file is corrupted, if so use sp_attach_single_file_db and then bringing it back online. but i am not able to get exact procedure for this. can anybody help.

    thanks in advance.

  • March 24, 2008 at 2:06 pm

    #793212

    Sure we can help. But first we need to know why the database is suspect. Please have a look in the SQL Server error log. There will very likely be something in there indicating why SQL's marked the database as suspect.

    Once we know the cause, we can suggest an appropriate solution.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 3, 2008 at 8:08 pm

    #798187

    database is on windows nt server. its a terminal sever. due to heavy network activity the system dumped. on restart sql server is showing suspect db. but error logs i checked, but am not able to make anything out of it

  • April 4, 2008 at 12:13 am

    #798213

    Have a look through the error log and post any sections that look like tehy contain errors or that you don't understand.

    If you like, zip the entire error log up and attach it to your post (providing it's not several MB in size, if it is the earliest 200 or so rows of the latest error log file will probably contain the info).

    The thing is, to help you we need to know why SQL has decided to mark that db suspect. That info should be in the error log somewhere

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 19, 2014 at 12:12 am

    #1707228

    If the Database is in Suspect mode then follow the below steps to recover ur DB

    >Stop the SQL Service

    >>Copy the MDF and LDF file and place in other safe location

    >>>Then Start SQL service

    >>>>Now also the Database will be in suspect mode only

    >>>>>Drop are Detach the Database

    >>>>>>Then Replace the MDF and LDF Files where it was Previously

    >>>>>>>And Attach DB by Selecting the MDF File....

    Regards
    Chowdary...

  • April 19, 2014 at 6:28 am

    #1707252

    Jagadeesh Chowdary (4/19/2014)

    If the Database is in Suspect mode then follow the below steps to recover ur DB

    >Stop the SQL Service

    >>Copy the MDF and LDF file and place in other safe location

    >>>Then Start SQL service

    >>>>Now also the Database will be in suspect mode only

    >>>>>Drop are Detach the Database

    >>>>>>Then Replace the MDF and LDF Files where it was Previously

    >>>>>>>And Attach DB by Selecting the MDF File....

    First, bad advice if you haven't determined why the database is suspect in the first place.

    Second, it may simply be better to restore from a known good backup (full + t-logs).

    Third, this is a 6 year old thread.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply