Post reply

Suspect Mode

  • October 17, 2005 at 9:13 am

    #110225

    My database is showing me Suspect mode????

    I don't have Backup of that Database. Can someone help me. This is in development DB

     

    Thanks

    Nita

     

  • October 17, 2005 at 10:27 am

    #597671

    This usually works for me....detach the database and then re-attach it.

    Then once it is successfully attached, BACK IT UP.

    -SQLBill

  • October 17, 2005 at 11:39 am

    #597702

    If its a dev box are you looking to get data back or code, if its code then you could Reset the Suspect Status and try and run a dbcc check and fix, or simply script out the objects/code that you can salvage.

    Look in bol for "Resetting the Suspect Status",

  • October 18, 2005 at 3:19 am

    #597856

    how about trying :

    alter database yourdb set online

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • October 18, 2005 at 3:39 am

    #597858

    Hi Nita,

    The Best way is to Detach the database and reatach it.

    Some of the Solutions include :-

    1. If the sytem dbs are ok but the users dbs are suspect maybe the log drive didn't boot on server reboot,or the log drive has become disconnected.

    Reconnect the log drive and restart the server

    2. If you've lost the log drive for ever or just have the mdf file use

    EXEC sp_attach_single_file_db @dbname = 'pubs',

    @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

    Format is sp_attach_single_file_db {dbname},{physical location of mdf file}

    It says the database should have been detached properly but experiance indicates this is not always the case.

    This command rebuilds a new log file

    3. Removing the suspect status

    sp_resetstatus dbname

    This changes the status bit in sysdatabases .. the effective command is :-

    update master.dbo.sysdatabases set status=status^256 where name= dbname

    4. If you haven't a recent backup then you can do the following. Look in your logs first, to see if is autorecovering.

    If nothing is in --the logs, run sp_who2 to see if there is a rollback on the db in question.

    If there is leave it it will 'hopefully' recover.

    If not, and as reset status will not work you'll have to set the db to emergency mode

    update sysdatabases set status = 32768

    Then set the db to single user

    exec sp_dboption 'yourdb', 'single user', 'true'

    Then run dbcc checkdb ('yourdb', REPAIR_REBUILD)

    Set single user to false.

    --

    --P.S Don't reboot the server or restart SQL!!

    Hope so this solves your Problem......

    Thanks,

    Kindest Regards,

    Jeetendra

  • October 18, 2005 at 7:52 am

    #597927

    Thanks It worked thanks for the valuable script and explanation.

    Thanks once again

    Nita

     

Viewing 6 posts - 1 through 5 (of 5 total)

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