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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


migration rejected from Dec 14 '15 at 19:16

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Max Vernon, Shawn Melton, Phil, RolandoMySQLDBA, ypercubeᵀᴹ Dec 14 '15 at 19:16

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Shawn Melton, Phil, RolandoMySQLDBA, ypercubeᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the 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
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