Database is in Transition state

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

2 Answers

Check this out. This will help you release locks. Works great!

use this

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; 

and then
for each processnumber

kill <processnumber> 

Check out this article.

I use TSQL most of the time, so I have not run into this issue yet.

What version is the SQL Server database and at what patch level?

Next time, do a usp_who2 to see what threads are running.

Since the output is in a table, you can search by database.

Kill all threads using the database before the trying the ALTER statement.

A night about 6 months ago, I had a terrible time getting a 2000 database offline due to an application constantly hitting it. I eventually disabled the user account so I would not get any more logins.