Different states/Modes of database in SQL Server

Share us

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.

  1. Online Mode
  2. Offline Mode
  3. Restoring Mode
  4. Recovery Mode
  5. Recovery Pending Mode
  6. Suspect Mode
  7. Emergency Mode

Online 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.

Online_DB

Offline Mode

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.

Offline

Restoring Mode

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

Restoring_DB

Recovery Mode

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.

MSDB_Recovery Pending

Suspect Mode

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.

Suspect_Mode

Emergency Mode

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.

Emergency_Mode_DB

 

Feel free to share your view, comments and feedback.

3 Commentsto Different states/Modes of database in SQL Server

  1. Hareesh Vaddi says:

    Nice info @Suraj and keep on focusing more elaborate info..

  2. sneha bhadra says:

    Nice

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.