"Database is in transition" Error

Repro

1. Open up SSMS

2. Type in the following into a new query window

   use <YourDatabase>;
   go
   

3. Go over to Object Explorer (SSMS) and right-click on <YourDatabase> -> Tasks -> Take Offline

4. Open up a second new query window and type the following:

   use <YourDatabase>;
   go
   

You will be prompted with the following message:

Msg 952, Level 16, State 1, Line 1
Database 'TestDb1' is in transition. Try the statement later.

The reason this is happening can be found from a similar diagnostic query to the one below:

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('<YourDatabase>')
order by request_session_id;

For what it's worth, you don't need Object Explorer to reproduce this error. You just need a blocked request that is attempting the same operation (in this case, take the database offline). See the below screenshot for the three steps in T-SQL:

What you will most likely see is your Object Explorer session being blocked by another session (shown by blocking_session_id). That Object Explorer session will be trying to get an exclusive lock (X) on the database. In the above repro's case, the Object Explorer session was granted an update lock (U) and attempting to convert to an exclusive lock (X). It had a wait_type of LCK_M_X, blocked by our session that was represented by the first query window (the use <YourDatabase> grabs a shared lock (S) on the database).

And then this error came about from yet another session trying to get a lock, and this error message results in the denial of a session to get access to a database that is attempting to transition into a different state (in this case, state of online to offline transition).

What should you do next time?

First off, don't panic and don't start dropping databases. You need to take a troubleshooting approach (with a similar diagnostic query like the one above) to find out why you're seeing what you're seeing. With a message like that, or when something appears "hung", you should automatically assume a lack of concurrency and start digging into blocking (sys.dm_tran_locks is a good start).

As a side note, I truly do believe that you are best to find out the root of a problem before taking any random action. Not just with this operation, but that goes for all behavior that you don't expect. Knowing what was really causing your problem, it's obvious it really was no big deal. You basically had a blocking chain, and the parent blocker was something you most likely could have just issued a KILL on, or if it was a session's request that you didn't want to KILL then you could have waited until it completed. Either way, you would have had the knowledge to make the right and prudent decision given your particular scenario (rollback or wait for commit).

Another thing worth noting, this is one of the reasons why I always opt for the T-SQL alternative instead of a GUI. You know exactly what you are executing with T-SQL and what SQL Server is doing. After all, you issued the explicit command. When you use a GUI, the actual T-SQL is going to be an abstraction. In this case, I looked at the blocked Object Explorer's attempt to take the database offline and it was ALTER DATABASE <YourDatabase> SET OFFLINE. There was no attempt to rollback, which is why it was indefinitely waiting. In your case, if you wanted to rollback sessions that had locks on that database, your ALTER DATABASE ... SET OFFLINE WITH ROLLBACK IMMEDIATE would have most likely sufficed if you had made the initial determination that rollback was okay.

Simply closing SQL Server Management Studio (SSMS) and reopening fixed the issue for me.