none
database in suspect mode

Answers

All replies

  • http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

     

    AND/OR

    1 Review the SQL Server and Windows error logs to see if you can find where the problem occured. 

    2 Start SQL Server in single user mode. 

    3 Go to your control panel and services. 

    4 Stop SQL Server 

    5 Add the -m switch in the parameters pane below. 

    6 Start SQL Server 

    7 Run sp_resetstatus with the @dbname parameter. (ie : sp_resetstatus @dbname = "pubs") 

    8 Perform detailed DBCC checks (CHECKDB, CHECKALLOC, etc) 

    9 Run a few random queries to see if you experience any problems. 

     

    If no problems occur, stop and start SQL Server and open the database to production. 

    As an absolute last resort, you can place your database in emergency mode. By placing it in this mode,

     you will be allowed to copy data out of the database, 

    even if the data is corrupt. To place your database in emergency mode, 

    use the following command: 

     

    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


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 14, 2011 2:26 PM
  • Have a look on this article.

    http://sql-server-recovery.blogspot.com/2010/10/repair-restore-sql-server-database-from.html


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
    Friday, July 15, 2011 10:27 AM
  • In general, that would mean restoring from a healthy backup. Before you try something like repair with allow data loss or forcing sql server to rebuild a ldf file, I suggest you read: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/14/why-we-never-want-to-trick-sql-server-into-rebuilding-a-log-file.aspx
    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, July 15, 2011 11:16 AM
    Moderator
  • Do you know why the database was marked as suspect?  There should be a related message in the SQL Server error log.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, July 15, 2011 12:11 PM
  • Hi,

    If your database is very big better to go for restore.

    If it is small.

    1.  Take the database into single user, Emergency mode.

    2. Run DBCC Checkdb to find out the reason any page corruptions are there.

    3. Repair db with REPAIR_ALLOW_DATA_LOSS / REPAIR_REBUILD / REPAIR_FAST based on your corruption.

    4. take the database into mutli user mode

    5. reset the status with sp_resetstatus

    6. Run DBCC Checkdb for cross verification.

     

    Friday, July 15, 2011 1:50 PM
  • I insist that any type of repair with allow data loss or rebuld of log is something that should not be done except in absolutely emergency. Again, details in http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/14/why-we-never-want-to-trick-sql-server-into-rebuilding-a-log-file.aspx.

    Checkdb will and can not catch all these types of problems. We don't know the state of the database!


    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, July 15, 2011 3:10 PM
    Moderator
  • Here is my take on this :

     

    1. Try to bring the database online:

    alter database <database_name> set online.

    2. If it fails then check the error message and errorlogs.

    here it becomes a very open ended question about what to do next. Will explain in brief about what could be the next steps:

    3. take the database in emergency mode 

    alter database <database_name> set emergency

    4. run dbcc checkdb and get the information about corruption of the database.

    From here it will be totally based on the kind of coruption you have e.g. logical or physical

    and also look for messages 824,823 if they exist then it's due to hardware so better apply the backups and get the database up online.

    if not then you could post the error message from DBCC checkdb and error logs.  Because it's difficult to say what kind of issue has occured in your database and for each issue there is different way to troubleshoot. 

     

    P.S. Best approach is to apply the backups and get the database up and running.  

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Saturday, July 16, 2011 9:20 AM
  • Hi Shan,

    Already experts answered your question.

    There are multiple reasons for database suspect status.To provide best solution we need the reason behind this

    for example some times if your are restoring a database mean while it to suspect mode ,causes could be

    1.backup files corruption

    3.Network break (if you are restoring from remote path)

    2.Low disk space in destination (yes it sounds silly ,but can be cause)

    Most worst situation disk corruption also can be cause for database suspect .If this is the reason.you are in a problem.

    Before starting resolution ,please find out the root cause .This approach will save your time and efforts.

    All the best.


    Sivaprasad.L Together We can Achieve
    Saturday, July 16, 2011 9:45 AM