Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for April, 2016

Database ‘DatabaseName’ is in transition. Try the statement later Msg 952, Level 16, State 1, Line 1

Posted by rahmanagoro on April 4, 2016

I bumped onto this error message when our monitoring tool started to report errors with a SQL agent job. On taking a close look at the error, I found that whenever I tried to connect to the database, I simply got the error:

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

Digging deeper into the issue and also the error information, i made some interesting discoveries. It appears that we have a blocking chain going on within the database, our environment is  such that database owners can choose to detach databases etc.

I can the following code to check for blocking:

 request_sql_text = st.text,
 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('DatabaseName')

It turned out that one user wanted to detach the database whilst another user just started to issue a query on the same database, this was the reason for the error and the change of the transition state. One way to avoid such issue before detaching a database is to take the following steps.

  • Personally, I do like using code/T-SQL to run commands rather then the good old GUI, I can see what i am doing and I can also see if my actions are getting stuck, rather than wondering if my Pc is slow etc.
  • Secondly, when detaching a database, if other connections can be rolled back. The code below can be issued.
ALTER DATABASE DatabaseName SET offline with rollback immediate

The issue

I could see the blocking chain, from the SQL query above which checks the locks. Multiple sessions wanted to connect to a database that was about to change its transition to offline and then detached.

What I simply did was kill one of the sessions which was causing the blocking.





Posted in Management, Scripts, SQL 2008, SQL Administration | Leave a Comment »