SQL Server Database States – simple talk but not simpler

Dear Friends,

Very recently while imparting training to junior DBAs someone from them asked me what are the different states of a database and how they can move into each stage? Well it sounds like a very basic question but as we understand this indeed an important one which should be very clear to all of us what may help us in troubleshooting various issues we work on. I’m sure most of you are already aware of it however my post may be useful to the ones with similar questions in their mind.

I’ve used following diagram in order to explain SQL Server database states;

1_SQL_Server_Database_States_simple_talk_but_not_simpler

To begin with, when database is started at server startup or when created or attached the state of the database is temporarily changed to RECOVERING. If there is an issue with resource such as database files or system resource limitations which may be preventing it from starting, the state changes to RECOVERY_PENDING and this state is persisted. As such when you have identified and resolved the underlying issue; user must have to use ALTER DATABASE <db_name> SET ONLINE to make the database functional.

If there are no issues with resources, the engine tries to run recovery on the database and if successful the database state changes to ONLINE and persisted but if recovery fails for the database state is changed to SUSPECT but not persisted (from SQL Server 2005 onwards). Please note; SUSPECT state is temporary so if you restart the server recovery runs again on the database so you have the option to have a look into the errorlog to find out the underlying reason then may troubleshoot appropriately.

   

When the database is ONLINE, you can use ALTER DATABASE <db_name> SET OFFLINE to OFFLINE the database which is again persisted. You are free to use ALTER DATABASE <db_name> SET EMERGENCY on any database states that are persisted (please consider your environment) and to add here EMERGENCY state of the database is also persisted (SQL Server 2005 onwards).

Now, what happens when you restore any database from a backup? Well, in such scenario initially database state is changed to RESTORING and when all pages are restored it moves to RECOVERING followed by normal recovery process and same as normal startup (please refer to the diagram above).

You are correct this diagram (used above) may not represent all possible scenarios for change of state for a database however it covers the common ones.

I hope this explains database states in short. Let me know if you have any suggestions which can help us to understand this better.

References

Database States: http://technet.microsoft.com/en-us/library/ms190442(v=sql.100).aspx

ALTER DATABASE SET Options: http://technet.microsoft.com/en-us/library/bb522682(v=sql.100).aspx

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.