Different states/Modes of database in SQL Server
In this article, I want to explain about different states or sometimes we call modes of database in SQL Server. We have different modes of database in SQL Server. These are mentioned below. I will go through each mode one by one.
- Online Mode
- Offline Mode
- Restoring Mode
- Recovery Mode
- Recovery Pending Mode
- Suspect Mode
- Emergency Mode
When a database is in ONLINE mode the database is available for access. This is the healthy state of the database and database is capable of fulfilling the request fired into it.
When a database is in OFFLINE mode then the database is not accessible for end user connections. We make the database offline when we get decommission request from customer side.
When a database is in RESTORING mode it means restoring of database is going on and at that time database will be not available to fulfill any request fired into it. To remove the database from restoring state,when restoring is done successfully, we use below script: Restore database [YourDatabaseName]with Recovery
When a database is in RECOVERING mode it means it is in the process of recovery and it will become automatically online for user connectivity. In case of a failure the database will become SUSPECT and become unable for use until a database repair is done by DBA.
Recovery Pending Mode
When a database is in RECOVERY PENDING mode it means SQL Server has encountered something unwanted error, a resource related error may occur during recovery. In such scenarios, we need to re-start the DB services and it comes online. If issues persist, then we need to take further analysis and do fixes.
When a database is in SUSPECT mode it means the database is completely inaccessible by end users. Reason may be, database has been damaged. In such scenarios, we need to repair the database to make it online and accessible. In my earlier blog, I have mentioned how to repair suspect state database, you can refer that.
When a database is in Emergency mode, it means a someone has changed the status of the database to EMERGENCY. This can be done only by the user having sysadmin privileges. In Emergency mode database will remain in single mode and the database can be repaired or restored. Also database will remain readonly mode. In this state of database, Suspect database is repaired. This is how database looks when it is in Emergency Mode.
Feel free to share your view, comments and feedback.