SQL Server Database States May 4th, 2010

Vinod Kumar

Last year at TechEd India 2009 I talked about Recovery of database with SQL Server. In SQL Server 2005+, there are 7 database states. And there is a method to analyze these database states, which I will discuss later. At any point of time, a database can only be in one state.


A database changes its state after an operation that happens automatically due to some sequence of events on the server. Some of state changes result from manual commands being executed. The current state of a database is exposed by the catalog view sys.databases using 2 columns: state and state_desc. The state column shows the state of the database using the integer code that represents a specific state. The state_desc shows the verbose information that pertains to the integer code shown by the state column. Since this column is present, there is no more a need to decode the various state bits as we used to do in previous versions of the product.

Maybe in a future post, I will try to talk about each of these database states and some of the significance of moving the states from one to another … There is a method to madness to this whole state management.

Which is the database error state you have encountered most of the time?

Tags: , , , , , ,

This entry was posted on Tuesday, May 4th, 2010 at 14:13 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

8 Responses to “SQL Server Database States”

  1. Harry says:

    Excellent stuff! Thanks a lot for sharing Vinod….

  2. fou says:

    Great tips! I will try it definitely

    thanks for sharing this!

  3. business says:

    nice post. thanks.

  4. SEO says:

    very good information you write it very clean. I’m very lucky to get this information from you.

  5. 黑帽seo says:

    Thanks for good information that comes out to


  6. vinodkumar says:

    Venkat – THanks for attending my session at TechEd. The PPT is available at – http://www.slideshare.net/guesta1b3b39/data-recovery-consistency-with-check-db … Please go through the resources where the session was mostly from Paul Randal’s blog posts.

  7. Venkat says:

    Hi Vinod,

    I have attended the Tech.ed sessions conducted in Bangalore this year. It was my pleasure to meet you at the event. I was very much impressed by the Backup and recovery session conducted by you. If there is no objection I would like to request you to share the PPT’s and the SQL scripts which was used by you in the session. It would help me a lot to prepare more and enlighten my SQL skills in future.



  8. Pradeep says:

    Hi Vinod,

    The session on SQL query tips was very good, i got to know good techniques. Can you please send me or provide the sql script of the tips you showed in GIDS 2010 in April.


Leave a Reply