Sql-server – Database in transition

sql server

I have a database that has been locked in transition for over 24 hours. I found a similar thread that discusses how to find the commands that are running but I do not know how to unlock the database. How can I resolve this transition state?

Query to get currently running commands:

use _MYDB_
go

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    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('_MYDB_')
order by request_session_id;

Results:
Results from the query:

Best Answer

Attempting to kill the offending process failed with the same error. In the end I had to reboot the server to get by the issue.

Related Question