SUSPECT DB

  • 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.

  • 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
  • Please also look in the error logs and there should be some clue about why they are suspect.

  • 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.

  • 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
  • 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.

  • 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
  • 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?

  • 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

  • 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.

  • 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
  • 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

  • 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
  • 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...

  • 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