Some of the reasons why an SQL Server database can be marked as SUSPECT

  • Database could have been corrupted.
  • There is not enough space available for the SQL Server to recover the database during startup.
  • Database cannot be opened due to inaccessible files or insufficient memory or disk space.
  • Database files are being held by operating system, third party backup software etc.
  • Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

Step 1 -  First , you need to go to Microsoft Management SQL Tools to check if the database stated as "Suspect". 


Step 2 -  After confirm that it is a "Suspect" database, login to Microsoft SQL Management Tools with your login instance name e.g. SERVER\QNEBSS with the user name of 'sa' and password 'QnE123!@#'


Step 3 - Right Click Database > Click New Query and copy the script below Replace the <database> with your database name. Click Execute to run the script


Step 4 - Insert ALTER DATABASE <database> SET EMERGENCY, Click Execute  


Step 5 - After finish, replace following sciprt and click Execute button. DBCC CHECKDB (<database>)


Step 6 - After finish, replace following sciprt and click Execute button. ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE


Step 7 -  After finish, replace following sciprt and click Execute button. DBCC CHECKDB (<database>, REPAIR_ALLOW_DATA_LOSS)


Step 8 - After finish, replace following sciprt and click Execute button. ALTER DATABASE <database> SET MULTI_USER

After finish, you may try to login to the database.


For more easy to copy the script:

ALTER DATABASE <database> SET EMERGENCY

DBCC CHECKDB (<database>)

ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB (<database>, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE <database> SET MULTI_USER


THANKS.