Can't Restore DB Because of Active Connections

Question

I have a DB I would like to restore. There are other active connections on the DB. I tried using sp_who2 to find those connections and manually kill them. Unfortunately, this did not solve the problem. I am now running the following code to set the DB to single user mode

alter database mydb
set single_user
with rollback immediate

This code runs for several hours and does not complete. Not sure if something is holding this command up. Is there another way to restore my DB?

Answer 1

(I know this isn't an answer; I've added this as an "answer" instead of a "comment" to make the code easier to read)

Further to Aaron's point of checking sys.dm_exec_requests to see what spid is blocking your restore, you could use the following query:

    WITH Blockers(blocking_session_id) AS
    (
        SELECT blocking_session_id 
        FROM sys.dm_exec_requests 
        WHERE blocking_session_id  0
    )
    SELECT DISTINCT CASE WHEN COALESCE(r.blocking_session_id,0) = 0 THEN 'X' ELSE '' END AS LeadBlocker, s.session_id, HOST_NAME, program_name, login_name, s.status, last_request_start_time,  DB_NAME(t.dbid) AS [DATABASE], OBJECT_NAME(t.objectid) AS [ObjectName], t.text AS SQLCommand
    FROM sys.dm_exec_sessions s 
        INNER JOIN Blockers ON s.session_id = Blockers.blocking_session_id
        INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    ORDER BY CASE WHEN COALESCE(r.blocking_session_id,0) = 0 THEN 'X' ELSE '' END DESC;