Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
When you say 'does not complete' do you mean it reports an error, or you are stopping it? –  Max Vernon Aug 28 '12 at 21:52
    
Have you checked the Activity Monitor? There may be some information there in the Processes or Resource Waits that you may be able to use to track down the open connections. –  TexasTubbs Aug 28 '12 at 21:55
    
Hi Max, I ran the code for about 2 hours and cancelled it –  Lloyd Banks Aug 28 '12 at 21:55
2  
Try it again and check sys.dm_exec_requests, you should see at least one spid that is running or blocked / blocking. It's possible that a rollback has initiated for that user but the rollback is taking a long time. –  Aaron Bertrand Aug 28 '12 at 23:28
    
Are you using a 3rd party tool or scripts to do the restore? If your able to restore from a .bak file using SQL Management Studio there is a check box under options to 'Close existing connections to destination database'. Have you tried that? –  RThomas Aug 28 '12 at 23:58
show 1 more comment

migrated from stackoverflow.com Aug 28 '12 at 22:52

This question came from our site for professional and enthusiast programmers.

1 Answer

(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;


share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.