Sql-server – “Database is in transition” Error

backuprestoresql serversql-server-2008-r2

Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS –> Tasks –> Take Offline so I could restore the database.

A small pop up window appeared and showed Query Executing..... for sometime and then threw an error saying Database is in use cannot take it offline. From which I gathered there are some active connections to that database so I tried to execute the following query 

USE master
GO
ALTER DATABASE My_DatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Again at this point the SSMS showed Query Executing..... for a sometime and then threw the following error:

Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'My_DatabaseName'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After this I could not connect to the database through SSMS. and when I tried to Take it offline using SSMS it threw an error saying:

Database is in Transition. Try later .....

At this point I simply could'nt touch the database anything I tried it returned the same error message Database is in Transition.

I got on google read some questions where people had faced similar issue and they recommended to close the SSMS and open it again, So did I and
Since it was only a dev server I just deleted the database using SSMS and restored on a new database.

My question is what could have possibly caused this ?? and how I can Avoid this to happen in future and if I ever end up in the same situation in future is there any other way of fixing it other then deleting the whole database ???

Thank you

Best Answer

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:

enter image description here

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.

Related Question