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.