SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

Different States of SQL Server Database

SQL Server Database can reside in one among the seven states. For example these can be ONLINE, OFFLINE, RESTORING, EMERGENCY, SUSPECT etc. This article explains each of these states and includes a T-SQL 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.

Use master
GO

SELECT
@@SERVERNAME AS [Server Name]
,NAME AS [Database Name]
,DATABASEPROPERTYEX(NAME, 'Recovery') AS [Recovery Model]
,DATABASEPROPERTYEX(NAME, 'Status') AS [Database Status]
FROM dbo.sysdatabases
ORDER BY NAME ASC
GO
How to Identify Current State of SQL Server Database

T-SQL Query to Identify Database which are Offline in SQL Server

Before we discuss different states of SQL Server Database we assume that you are aware of Different Recovery Mode in SQL Server.

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 accessible 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 accidentally 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 least the primary file group is suspect. 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

Conclusion

SQL Server Security in itself is a vast topic and in this article we have discussed few of the settings which can be easily implemented to improve the overall security of the SQL Servers which you manage day-to-day in your work.

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning