suspected database

  • how we can recover suspected database  if  its does n't have  backup

    tell me how  to recover

    balaji ramanar

  • Rule 1 always have a backup !!!

    These are the notes I have on suspect databases - in general terms don't expect too much, but these are the best known options i have.

    How to recover a suspect database.

    Top Tips

    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


    See also:-



    Use these where a database has been marked suspect due to insuffcient free space to expand

    check out bol for details


    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

    Emergency mode... ( if it's all really bad )

    Set the status of the database to 32768, this should allow you the chance to dts/bcp out data from the database.

    ( not tested )


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

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]

  • First I would try to determine WHY the database is being marked as suspect.  Here are a couple of reasons we have had the problem at my office:

    You have a SQL Server database that appears healthy and well [i.e. DBCC CheckDB reports nothing, and the physical disk have no issues], yet, with a high frequency, the database gets marked as 'Suspect'.


    Perhaps you have turned on the 'Auto Close' option of the database. Enabling this shuts down SQL Server's use of any files that the database uses, permitting other system activities (scandisk, backups, etc.) to have an exclusive lock on the file. When SQL tries to re-access the database and fails, it will mark the DB as suspect.

    Turn off the 'Auto Close' option, and you'll be in business again.


    An SQL Server database will not startup, and is marked supsect, because a DTC Transaction is in an unknown state.


    The transaction must be forced clear of the system before the database will be able to start up. If a two-phase transaction was taking place during the failure, the database is stuck waiting for a response from the second database which, unfortunately, is most likely not coming.

    To remove the transaction, start the Component Services MMC snap-in. Under the computer that the SQL Server is running on, expand teh Dsitributed Transactions folder, and then click the Transaction List folder item. In the right-pane, the transaction that is causing your problem should be displayed.

    With the SQL Server stopped, right-click the transaction and choose 'Abort.'

    Restarting the SQL Server now should permit your database to fully recover.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 3 (of 3 total)

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