SQL Server -- How to fix Database goes to suspect mode

olga

　　Problem:
　　Database goes to suspect mode
　　Root Cause:
　　Mostly, the database goes to the Suspect mode caused by the physical database files is corrupt. But there are some other reasons could also cause this issue,
　　such as the database file was renamed, or the hardware failure.  We could use this statement
　　“SELECT DATABASEPROPERTYEX('DB_NAME','STATUS')” just as Uri Dimant mentioned to check databases whether go to the Suspect mode,
　　if it is, this statement return “SUSPECT”.

　　When you see the your database in Suspect mode that a code red situation. Its not something that you>　　This post will show how to recover from suspect mode, but still go through SQL-server error logs and find out the root cause of the error.
　　Reason:
　　At start-up, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process  or if the file is missing, sql server start displaying error.
　　In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.
　　Possible reason for changed to suspect mode in sql server can be
　　The system cannot find the file specified.) during the creation/opening of physical device
　　Failed to open device where data or log file resides
　　SQL server went down/restarted in the middle of a transaction causes transactions log to be corrupted
　　SQL server can not access data or log file while coming online , because of you beloved antivirus
　　First Probability: Sometimes, when SQL Server data or log file gets misplaced or deleted while the SQL Server Database was in offline mode, then in that situation data cannot be recovered when you start SQL Server because of missing files. To recover SQL database from suspect mode under such circumstances, you need to place the missing files at their original location. In error log, the SQL Server error will provide correct name and path of the file that are deleted or missing from its location.

　　Second Probability: Another cause of SQL Server database being in suspect mode is corrupted transaction. In this case, you might lose data and a>　　Third Probability: Now, in case where you find corruption in data file, the likelihood is your Operating System or hardware is failing in their performance. For this type of failure, one must employ an extraneous solution to recover SQL database from suspect mode.
　　Troubleshoot &Solution:
　　1. Try to reset the db sp_resetstatus 'dbname'
　　EXEC sp_resetstatus 'DATABASE_NAME'
　　2. If db not comes online then follow the steps.
　　3. Take the db into emergency and single user mode.
　　4. check the database consistance if any error found repair the db.
　　ALTER DATABASE DATABASE_NAME SET EMERGENCY
　　DBCC checkdb('DATABASE_NAME')
　　ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
　　--DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS
　　--Some data will be loss after run the below command, be carefule to execute it.
　　DBCC CheckDB ('DATABASE_NAME', REPAIR_ALLOW_DATA_LOSS)
　　5. bring the db into multi user mode.
　　ALTER DATABASE DATABASE_NAME SET MULTI_USER
　　6. Finally reset the status.
　　EXEC sp_resetstatus 'DATABASE_NAME'
　　7. Check the DB status, it should be in normal status
　　SELECT NAME,STATE_DESC FROM SYS.DATABASES
　　WHERE STATE_DESC in ('SUSPECT','RECOVERY_PENDING')
　　GO
　　8. Rebuild Index for table
　　ALTER INDEX ALL ON [YourTableName] REBUILD
　　9. If the issue exist.
　　If the database is still marked as suspect after performing these steps, there may be other problems preventing the database from recovering.
　　At this point, only option left is either restore from a good backup or set the database to emergency mode and use the bulk copy program copy the data out.
　　Additionally, you may require performing one or more of the following actions:
　　Modify or update statistics.
　　Copy all recoverable data in the damaged table to a new table.
　　Drop and recreate one or more indexes.
　　Use 'sp_recompile' for recompiling your stored procedures and triggers.
　　Pre-action:
　　in case database goes to suspect mode can we get mail Or alert ? what actions we have to take db goes to suspect mode.
　　参考：
　　http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
　　http://www.codeproject.com/Articles/587460/Solution-To-The-MSSQL-Server-Suspect
　　https://www.mssqltips.com/sqlservertip/3191/how-to-recover-a-suspect-msdb-database-in-sql-server/
　　http://www.snapdba.com/2011/11/how-to-repair-a-sql-server-20052008-database-in-suspect-mode/#.WA73wXm7odk
　　https://social.msdn.microsoft.com/Forums/sqlserver/en-us/a8e9eceb-9e41-4853-a66c-f176b15208ee/suspect-mode?forum=sqltools
　　https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2291a9a3-ac59-4982-802f-b5450301c04c/what-is-suspect-mode-database?forum=sqlgetstarted
　　https://support.microsoft.com/en-us/kb/180500
　　http://blog.systoolsgroup.com/sql-suspect-database-recovery.html
　　http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
　　http://sqlusa.com/bestpractices2005/markedsuspect/
　　http://www.stellarsqldatabaserecovery.com/repair-suspect-sql-server.php
　　http://social.technet.microsoft.com/wiki/contents/articles/14616.troublshooting-sql-database-suspect-mode.aspx
　　http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html
　　https://support.managed.com/kb/a398/how-to-repair-a-suspect-database-in-mssql.aspx
　　脚本：
　　检查Suspect DB状态：
　　CheckDBStatus.ps1
　　修复Suspect DB:
　　RepaireSuspectDB.ps1