How to repair a 'SUSPECT' Database in Optimum

Created by Cheng Kah Poh, Modified on Fri, 4 Sep, 2020 at 2:04 PM by Cheng Kah Poh

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.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article