SQL Server: "Error 952 Database is in Transition"

Hello All,

Today I have just faced this problem when I was trying to log-in to a database. I can't even execute sp_who command to view who is doing wrong. Then I execute below command to see what was happening with my instance.

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

Here CROSS APPLY to sys.dm_exec_sql_text Table-valued function he was able to see the text of each SQL batch in the result set.

I found the below cause of showing subject mentioned error

3,0,64,ALTER DATABASE, ALTER DATABASE dbname SET SINGLE_USER; ,95,18399,0,DFC\SQLSERVER,DFCGSSQL04,.Net SqlClient Data Provider,2,suspended,master,LCK_M_X,18399806,0,0,10

I did the following stuffs to resolve this matter. First what actually that session is doing and then kill

DBCC INPUTBUFFER (95);

KILL 95

This should be enough. Some body sugests like this but I think that is not required.

USE master

GO
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE dbname set online


Source:
http://www.sqlservercentral.com/blogs/sqldownsouth/2009/08/12/database-is-in-transition/