This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Recently, I shared one article about Database is stuck in “In Recovery” mode after the restart of SQL Server.
SQL Server: After Restart, Database in “In Recovery” status, Can’t Access it
In this post, I am going to share necessary steps to recover a database from the SUSPECT Mode. If your database in SUSPECT mode, you cannot access it.
There are several reasons behind the SUSPECT Mode like a hardware issue, transaction log file is corrupt, missing transaction log file, improper shutdown or restart of SQL Server, page or disk is corrupt and other much more.
You can check the SQL Server error logs which give you more details.
Action steps to recover a database from SUSPECT Mode to ONLINE Mode:
Change the database status to EMERGENCY Mode:
|
1 |
ALTER DATABASE database_name SET EMERGENCY |
Now administrator can access your database and execute DBCC CHECKDB next:
|
1 |
DBCC CHECKDB('database_name') |
Bring the SQL Database into Single user mode:
|
1 |
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE |
Re-Run DBCC CHECKDB with repari options:
It repairs the database and fixes the error, but there are chance of data loss.
|
1 |
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS) |
If you get success message, change the database mode from single-user mode to multi-user mode.
|
1 |
ALTER DATABASE database_name SET MULTI_USER |
