SQL Server Database not accessible suspect mode.
Introduction:-
This document provides a concise guide for troubleshooting and resolving SQL Server databases in suspect mode, which prevents user access. It outlines steps for pre-checks, setting the database to emergency mode, running health checks using DBCC CHECKDB, and restoring the database to online mode. By following these steps, the database can be successfully recovered and returned to normal operations, ensuring availability and functionality.
Pre-Checks:
Review the SQL Server and Windows event logs:
- Connect to the respective SQL Server and check error logs for any reported issues.
- Connect to the RDP server and verify the event logs.
- Review the server restart time; if a restart has occurred, ensure all databases are healthy.
Database suspect mode:-
- We found that the database is in suspect mode, and users are unable to access it.
Step 1:- Set the database to emergency mode:
USE master;
GO
ALTER DATABASE [TEST] SET EMERGENCY;
GO
Step 2:- Run DBCC CHECKDB to verify the database health:
DBCC CHECKDB (TEST);
GO
Step 3:- Review the DBCC CHECKDB output:
- If no errors are found, proceed to set the database back to online mode.
Step 4:- Bring the database back to online mode:
ALTER DATABASE [TEST] SET ONLINE;
Step 5:- Run DBCC CHECKDB again to verify the database health:
DBCC CHECKDB (TEST);
GO
- Review the logs for each process to confirm successful recovery.
Conclusion:-
The database issue in suspect mode was resolved by setting it to emergency mode, running health checks, and bringing it back online. No errors were found during verification. The database is now accessible, and normal operations have resumed.
Recent Posts
