Database is in Transition

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:

SELECT
    r.scheduler_id,
    r.cpu_time,
    r.session_id,
    r.command Command,
    t.text SQL_Statment,
    r.blocking_session_id Blocking_Session_ID,
    r.total_elapsed_time/1000 Total_Elapsed_Time_Seconds,
    r.cpu_time CPU_Time,
    s.login_name Login_Name,
    s.[host_name] [Host_Name],
    s.[program_name] [Program_name],
    s.memory_usage Memory_Usage,
    r.status [Status],
    db_name(r.database_id) Database_Name,
    r.wait_type Wait_Type,
    r.wait_time Wait_time,
    r.reads Reads,
    r.writes Writes,
    r.logical_reads Logical_Reads
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
WHERE
    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
Patrick LeBlanc
SQL Down South