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 face everyday. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file).
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 reliable backup can help to deal with the situation. The basis behind the issue is guarantee taken by SQL Server to ascertain consistency of transaction under ACID property of Relation Database Management System. This happens basically when SQL Server is shut down or restarted unexpectedly in between the transaction and at the time of rejoining, complete transaction could not take place. At this stage, to recover SQL database from suspect mode, a good backup should be utilized.
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
