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
Feedback sent
We appreciate your effort and will try to fix the article