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;
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 Twitter | Follow me on FaceBook
