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
To resolve this issue run the commands listed below,
What the above command do is perform a series of step to check the logical & physical consistency of the database and tries to repair. Very first step is to turns off the suspect flag on a database, you can achieve his by using sytem stored procedure sp_resetstatus. Using this procedure change the suspect flag to emergency. Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it. Then perform a consistency check by executing DBCC command on the master database. Next step is to rollback any transactions if any are present in the database and bring the database into Single User mode. Run the repair and finally bring database to Multi User mode.
EXEC sp_resetstatus ‘DATABASE_NAME’; ALTER DATABASE DATABASE_NAME SET EMERGENCY DBCC checkdb(‘DATABASE_NAME’) ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB (‘DATABASE_NAME’, REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE DATABASE_NAME SET MULTI_USER
Remember sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.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.