How to repair a 'SUSPECT' Database in Optimum

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.