SQL Server Performance

How to bring suspect database to online mode

Discussion in 'General DBA Questions' started by r_gautham2001, Nov 10, 2007.

  1. r_gautham2001 New Member

    HI All,
    I have a suspect database which regularly goes into suspect mode. BUt when the server restarts it comes online again and after some time it goes offline again.
    First Request: Let me know what are the possible ways to get the suspect database to online mode.
    2) what could cause that database to go in to suspect mode.
    3) how tp find the root cause.
    Regards,
    Gautham
  2. Luis Martin Moderator

    sp_resetstatus

    Resets the status of a suspect database.
    Syntax

    sp_resetstatus [ @DBName = ] 'database'
    Arguments

    [@DBName =] 'database'
    Is the name of the database to reset. database is sysname, with no default.
    Return Code Values

    0 (success) or 1 (failure)
    Remarks

    sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sysdatabases. The SQL Server error log should be consulted and all problems resolved before running this procedure. Stop and restart SQL Server after executing sp_resetstatus.
    A database can become suspect for several reasons. Possible causes include denial of access to a database resource by the operating system, and the unavailability or corruption of one or more database files.
    Permissions

    Only members of the sysadmin fixed server role can execute sp_resetstatus.
    If it works, then run DBCC Checkdb to find out if there are some problem.
  3. satya Moderator

    That is not good for making a database suspect each and every time.
    Look at SQL server error log, event viewer log and all other logs that could give some information on why this database is becoming suspect. Also check the disks are in good condition.

Share This Page