In this article we look at the states in which the SQL Server database can operate.
There are seven states in which SQL Server can operate. All these states are denoted by differences in their performance, the results produced and the ease of use. A database can operate in only one of these states at any given time. You can find out the current state of your database by selecting state_desc column present in the sys.databases catalog view. There are different reasons for the occurrence of each of these SQL Server states. All the seven states are described below.
- Online: When the state of a database is Online, it means that it can be accessed at the moment, and is available. It also means that the state of Primary file group is essentially online, though the important undo phase of recovery might still be incomplete.
- Offline: This simply means that the database is unavailable, this usually happens due to explicit user action, and can’t be rectified till user action is taken. This can be explained with the instance of the database being offline during the transfer file to disk, and then again getting back online after completing the transfer.
- Restoring: When one or more than one files of primary filegroup or one or more than one files of secondary files is apparently being restored, the database is in ‘Restoring’ state and is unavailable.
- Recovering: This is a transient process; in-case of successful recovery database will be online automatically. In-case of a failure in recovery, it will become ‘Suspect’, and will thus be unavailable.
- Recovery Pending: This means that a resource related error has been encountered during recovery. This does not damage the database, but some files may go missing or system resource limitation may create problems while opening files. This requires additional action from the user to deal with the error and complete the recovery process. In this state also the database is unavailable.
- Suspect: This marks unavailability of database for user connection and shows that database is damaged or primary filegroup is suspect, this involves action by user to solve the problem.
- Emergency: This is done by the user them-self; a user can change the state of database to ‘Emergency’ and then it will operate on single user mode which may be repaired or even restored. The database operates as READ_ONLY, or logging is disabled, it is used primarily for the purpose of troubleshooting
These states have been introduced in the SQL Server due to security reasons and most of them need to be activated manually. In order to be sure that what is the right time and situation to activate any of the seven states, you have to be well versed with the SQL Server management, or take the help of a Database Administrator or a SQL expert. Doing it yourself without being sure about what to do and how to do can backfire and might also result in loss of data.
The emergency mode is often used as the starting point when you need to run a recovery
The emergency mode is typically initiated before attempting a recovery. While in most cases the recovery process may work smoothly, in some scenarios the database repair process may get stuck. If that happens, you need to use a specialized recover sql tool like DataNumen SQL Recovery to bring back the stored data. Powered with an insightful extraction engine, this remarkable software can bring back every possible data you have stored in the database including indexes and embedded objects with ease.
Author Introduction:
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/
Leave a Reply