Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by Steve Jones on 12 August 2009

Very nice, a good reminder to people of a query that can come in handy in many ways.

Posted by Robert Davis on 13 August 2009

Or just use the "With Rollback Immediate" flag to have it rollback all open transactions.

I.E.:  Alter Database <Database> Set Offline With Rollback Immediate;

Posted by Patrick LeBlanc on 14 August 2009

Thanks Robert

Posted by renatosacchetti on 24 January 2014

Nice post. I just got the same error and could not execute sp_who and sp_who2, but with this query i found what was wrong. Thanks a lot!!  

[]´s

Renato Calonico

Leave a Comment

Please register or log in to leave a comment.