Post reply

Database in sspect mode

  • July 8, 2009 at 5:08 am

    #95819

    My database is changed to suspect mode. Taken the backup of both mdf and ldf files immediately. As per some suggestions detached that database and while attaching it, it is not allowing and it is failing. Can any of you tell me how to bring that mdf file to normal.

    Thanks in advance

    Naveen

  • July 8, 2009 at 5:13 am

    #1021563

    Restore your backup and run

    DBCC Checkdb ('DBNAME') with ALL_ERRORMSGS

    to find out the issue.

    Look at this link for fix:-

    http://www.sqlservercentral.com/articles/65804/

  • July 8, 2009 at 5:32 am

    #1021571

    I dont have the backup to restore it

  • July 8, 2009 at 5:34 am

    #1021573

    Hi,

    Suspect database should not be detached and dont try to restart sql server also. As you had already detached the database you have very few options. Have a look into the error log why the database went into suspect mode. Depending on that you can proceed further

    Thanks

    Chandra Mohan N

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • July 8, 2009 at 5:56 am

    #1021582

    Now I have created a new database with same mdf and ldf file names. Then replaced those files with my old files. After that when I opened my Enterprise Manager I found that the database is now in suspect mode. Can you tell me how to bring it to normal from here.

    Thanks in advance

  • July 8, 2009 at 5:58 am

    #1021583

    Run DBCC Checkdb

    to find out what the error is

  • July 8, 2009 at 6:14 am

    #1021592

    When I executed the DBCC CHECKDB(DBNAME), getting the below error

    Database 'Test1' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

    Can you tell me what to do..

    Thanks in advance

  • July 8, 2009 at 6:15 am

    #1021593

    As panders suggested run CheckDB. According to the result you might be able to repair the database using checkdb with a repair option.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 8, 2009 at 6:24 am

    #1021597

    To everyone suggesting CheckDB, DBCC checkDB cannot be run on a database that is suspect.

    ekknaveen: Check the SQL error log. There should be an indication in there as to why the DB is suspect. Look for any relevent errors and post them here. Once we know why it's suspect then we can suggest a course of action

    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

  • July 8, 2009 at 6:36 am

    #1021610

    I exeucted DBCC CHECKDB (dbname, repair_rebuild) option but it is throwing the same error:

    Server: Msg 926, Level 10, State 1, Line 1

    Database 'Test1' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

  • July 8, 2009 at 6:36 am

    #1021611

    Sorry I did not notice that it was 2000

    Please check whether the following link helps you

    http://www.gerixsoft.com/blog/mssql/recovering-mssql-suspect-mode-emergency-mode-error-1813

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 8, 2009 at 6:47 am

    #1021616

    try setting the database as EMERGENCY:-

    ALTER DATABASE dbname SET EMERGENCY

    then see if you can run CHECKDB

  • July 8, 2009 at 6:54 am

    #1021625

    Or for SQL 2000

    To put the DB in emergency mode in 2000, you need to hack the sysdatabases table so the status field has the 'bypass recovery' bit set. You can do this by reconfiguring the server to allow updates. Do the following:

    sp_configure "allow updates",1

    reconfigure with override

    go

    update sysdatabases set status= 32768 where name = "mydb"

    go

    sp_configure "allow updates",0

    reconfigure with override

    go

  • July 8, 2009 at 7:03 am

    #1021630

    When I am performed the below steps I found the error "Could not find index entry for RID 16b676fd5b010000 in index page (1:116) index id 0, database dbname.

    1. changed the database from suspect to emergency

    2. crated a new database

    3. while exporting the data from emergency to new database i found the above error.

    Can you please tell me what it is and how to resolve it.

  • July 8, 2009 at 7:40 am

    #1021651

    Seems you have corruption on your index. id 0 suggests that you might loose some data if you repair. Did you try running checkDb?

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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