One of my clients called saying they were receiving the following error when trying to access a database: "Error 952 Database is in Transition".
So I asked, what was the last thing that you tried to do with the database. He said take it offline, and that it would not go offline. Ahhhhh. I asked him to run the following query:
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
r.session_id <> @@spid
This query returns one row per authenticated session on the SQL Server and by using the CROSS APPLY to the sys.dm_exec_sql_text Table-valued function he was able to see the text of each SQL batch in the result set. Before I could ask anything about the result he said, "there is a SPID that is blocked by something from the database that I was trying to take offline". Unfortunately, I did not get a screen shot of the result set before we fixed the problem. The blocking SPID was a query running in the database that he was trying to take offline. The SPID that was blocked was trying to access the database he was trying to take offline. I asked him to look at the text and let me know if we could kill the SPID. We were able to kill the SPID and the blocked session was finalized, either committed or aborted. Likely aborted since the database was taken offline immediately after
the blocking session was killed and his problem was resolved.
So if you are ever taking a database offline try ensure that it is idle or you could end up spending a little $$ to get someone to help fix your problem, unless you are a DBA of course :).
Talk to you soon
SQL Down South