Different States of SQL Server Database
Related Topics: SQL Server Disaster Recovery Tips>
Next Topic: SQL Server Indexing Articles & Tips
SQL Server Database can reside in one among the seven states. For example these can be ONLINE, OFFLINE, RESTORING, SUSPECT etc. This article explains each of these states and includes a TSQL code which can be used to identify the current state of a SQL Server Database.
How to Identify Current State of SQL Server Database
Execute the below query to identify the current state of all the SQL Server Database in your Instance.
@@SERVERNAME AS [Server Name]
,NAME AS [Database Name]
,DATABASEPROPERTYEX(NAME, 'Recovery') AS [Recovery Model]
,DATABASEPROPERTYEX(NAME, 'Status') AS [Database Status]
ORDER BY NAME ASC
Different States of SQL Server Database
A SQL Server Database is can only be in one specific state at a given time. Different States of SQL Server Database are:-
- ONLINE:- When a database is in ONLINE state the database is available for access. The primary filegroup is online eventhough the undo phase of recovery may not have been completed.
- OFFLINE:- When a database is in OFFLINE state then the database is not accessable for user connections. One can set the database to this state if you don’t want users to connect to the database. For example you have migrated the database to a new server and don’t want users to accidently connect to the Old SQL Server Database.
- RESTORING:- When a database is in RESTORING state then it means one or more files of the primary filegroup is been restored or one or more secondary files are being resotored offline.
- RECOVERING:- When a database is in RECOVERING state it means its 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 intervene and fixes the issues.
- RECOVERY PENDING: - When a database is in RECOVERY PENDING state it means SQL Server has encountered a resource related error during recovery. The database might be missing files. DBAs intervention is required in such a case.
- SUSPECT: - When a database is in SUSPECT state it means the database is unavailable for user connection. Database may be damaged or at leasr the primary filegroup is supect. DBAs intervention is required in such a case. Read the following article which explains “How to Repair SUSPECT Database in SQL Server”
- EMERGENCY: - When a database is in EMERGENCY state it means a user has changed the status of the database to EMERGENCY. In EMERGENCY mode database will remain in SINGLE_USER mode and the database can be repaired or restored. Database will remain READ_ONLY. You need to be a member of SYSADMIN role to set database in EMERGENCY mode. You make have to set the state of the database as EMERGENCY when the database is market as SUSPECT. Read the following article which explains “How to set Database in EMERGENCY state”
Reference: Database States on TechNet
• Improved Startup options in SQL Server 2012 and Later Versions
• How to Identify Deadlocks in SQL Server Using Trace Flags
• How to Enable BACKUP CHECKSUM in SQL Server Using Trace Flags
• How to Repair a Suspect Database in SQL Server
• How to Disable SQL Server from writing every successfully backup entry in SQL Server Error Logs
• SQL Server Disaster Recovery Tips for DBAs and Developers
Thank you for taking your time to read
this article. Let's be Connected....
More... Disaster Recovery Tips for DBAs and Developers
- Different Ways to Retrieve SQL Server Configuration Details
- How to Move TempDB Database Files to a New Drive in SQL Server
- Improved Startup Parameters in SQL Server 2012
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- How to Repair Suspect Database in SQL Server
- How to Connect to a Named Instance of SQL Server
- Introduction to SQL Server Configuration Manager
- SQL Server Disaster Recovery Tips for DBAs and Developers
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: May 03, 2016
Please leave your Valuable Comment or Let us know how this article helped you: