You may find a SQL Server database running in a specific state, like ONLINE, SUSPECT, OFFLINE, or other database state.
How to Check SQL Database State?
You can find the current state of all the SQL Server databases by selecting the “state_desc” column available in the “sys.databases” catalog view:
Below is a screenshot that shows all the databases and their current state. As you can see, majority of the databases are in an ONLINE state, and one database is in RESTORING state.
To check the current state of a specific database, use the DATABASEPROPERTYEX function with the ‘status’ property:
Now let's discuss in detail all the seven SQL database states, what happens when a database moves to a specific state, problems you may encounter, and their solutions.
Types of SQL Server Database States
1. ONLINE
A database in the ONLINE state is fully available and could be accessed for all the operations. In this state, the default (i.e., primary) filegroup is online, but the undo phase of database recovery might still need to be completed.
Essentially, a SQL database in ONLINE state is healthy and functions normally. Ideally, a database should move to this state when it is started on SQL Server.
2. OFFLINE
A SQL database in the OFFLINE state remains unavailable for user access. A database is set to OFFLINE by explicit user action. Changing a database state to OFFLINE is helpful in situations when you need to move the db files to a new drive or prevent users from accessing the db.
The following command is used to set the database state to OFFLINE:
You may also set a db to an OFFLINE state using SQL Server Management Studio (SSMS). To do so, right-click on the database, click Tasks, and then click on Take Offline, as you can see in the image below.
The database state of the database, in our case ‘DBLogTest’, is set to OFFLINE.
You can bring a database ONLINE by using the command:
Or, use SSMS to bring the db back online by right-clicking on it, choosing Tasks, and then clicking the Bring Online option.
Note: A SQL database may also become OFFLINE if it gets corrupted. In such a situation, you can try restoring the database from backup. If this fails, you will need to repair the database.
3. RESTORING
A database moves to the RESTORING state when the database restore process is initiated. This usually happens when the db is restored from backup. During this state, one or more data files (.mdf or .ndf) of the Primary filegroup gets restored, or one or more secondary files (.ndf) gets restored in the OFFLINE mode.
A database becomes unavailable when it goes into RESTORING state. Using "WITH RECOVERY" as the database restore option helps bring the database back online after completing the restoration process. However, when restoring multiple backup files using the "NORECOVERY" restore option, the db remains in the RESTORING mode unless the last backup file is reached using the WITH RECOVERY option.
For detailed information on how to perform database restore with RECOVERY and NORECOVERY, refer to this link.
Sometimes, the SQL database gets stuck in RESTORING state. You can try to drop your database and restore it from a good known backup copy in such a situation.
4. RECOVERING
A database state changes temporarily to RECOVERING when the SQL Server is restarted or when the database is attached to the server. The database is marked as "In Recovery" when it goes into the RECOVERING state and remains unavailable until the recovery process is completed.
When performing database recovery, the database may seem stuck in the RECOVERY mode. However, the database is still in recovery and undergoes three different phases that can be completed based on your database size. These phases comprise Analysis, Redo, and Undo phases.
A Dirty Page Table (DPT) gets created in the Analysis phase, which helps identify all the dirty pages during the server crash. Also, in this phase, an Active Transaction Table (ATT) is created to determine uncommitted transactions when the SQL Server stops.
In the Redo phase, all the committed transactions are rolled back.
Finally, a database goes into Undo phase, when any uncommitted transaction is rolled back.
At times, you may find that the database is stuck in the "In RECOVERY" state. Such a situation could occur due to a large-sized transaction log file, bug in the SQL Server, etc. In this case, you must wait patiently for the recovery process to complete. The database becomes ONLINE after the completion of the recovery process. If this process fails, the database state changes to SUSPECT or RECOVERY PENDING.
5. RECOVERY PENDING
When the database cannot come ONLINE, it is marked as "RECOVERY PENDING".
If you need to fix a database stuck in RECOVERY PENDING mode, refer to this link.
6. SUSPECT
When database recovery fails, or the database becomes corrupted, it goes into SUSPECT mode. A database marked as SUSPECT is unavailable for user access. However, you can make the database available using the 'Restore and Recovery' method or by running 'DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS'.
To know more about the database in the SUSPECT state and how to fix it, refer to this link.
7. EMERGENCY
The state of a database can be changed to EMERGENCY by a user to repair or restore the database. The database set to EMERGENCY state, enters SINGLE_USER mode and is marked as READ_ONLY, restricting the database access to the sysadmin role members.
The primary purpose of putting a database in this state is to troubleshoot issues with the database. For instance, a database that goes into SUSPECT mode can be set to the EMERGENCY state allowing read-only access to the database.
To set the db in an EMERGENCY state, use the following command:
Refresh the database, and you will see that it is set to EMERGENCY.
Once the database is in the EMERGENCY state, you can fix database corruption issue by executing the DBCC CHECKDB command. But, before running this command, ensure to set the db in SINGLE_USER mode by executing the following query:
The db will enter the SINGLE_USER mode. You can now run the DBCC CHECKDB command using “REPAIR_ALLOW_DATA_LOSS”.
Note: Executing the CHECKDB command with the repair option will remove corrupted data or indexes to make the database consistent but with some data loss. A better alternative to repairing the database without data loss is to use a SQL repair tool that helps fix the corrupted database and restore it to its original state.
After repairing the database, set it to MULTI_USER mode:
The db will be repaired and will get ONLINE.
How to Check SQL Error Log to Determine Reason behind Database State?
Looking into the SQL Server Error log can help you find why the database did not come online or is still in "In Recovery" or "SUSPECT" mode. To check SQL Error Log, follow these steps:
- Open SSMS, expand SQL Server Agent, and then expand Error Logs.
- Next, check the logs to find the reason behind why database went into suspect mode. Apply filter to quickly find out when a database went into RESTORING, SUSPECT, or any other state. For this, enter the database name in the “Message contains text” textbox under the General section. Next, select the Apply Filter checkbox and then click on OK.
In our case, we have entered ‘RecoveredDeletedData_Copy’ to determine why it went into RESTORING mode.
- Read the message given at the bottom of the screen to find why the db went into RESTORING state.
What to do If Database Won’t Come Online?
Try restoring the database from the most recent backup if it doesn't come online. If you don't have an updated backup, you may need to repair your database by running the DBCC CHECKDB command with a repair option.
Read this: How to Repair SQL Database using DBCC CHECKDB Command
Running the CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option can lead to data loss. A better solution is to use a SQL recovery tool that helps regain access to the SQL db with no added risk of data loss. Use Stellar Repair for MS SQL to restore your db with all its data intact.
Wrapping Up
This guide explained the different SQL Server database states, such as ONLINE, OFFLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT, and EMERGENCY. It discussed each of these states and how you can change to and from one state to another. Also, it talked about what you can do to restore a database when it becomes inaccessible after entering into a state.
If your database seems stuck in any of the seven database states, and nothing works for you, using Stellar Repair for MS SQL can prove to be a handy tool. In the event of database corruption, the SQL repair tool helps repair database files (.mdf/.ndf) and restore the db to its original form without any data loss.
9 min read
-1112.jpg)
