18

After clicking on "Take database offline" in management studio this message stays hang and won't close if you click on close.

https://i.imgur.com/KD6AROv.png

What's a good way to deal with stuck jobs like these in management studio ? Can you kill them via the activity monitor ? Should I seek what process is stopping this job from going through and terminate it ?

Improve this question
2
  • 5
    Find out what's blocking it, first. You can get this information from sys.dm_exec_requests. Also your job can do this in a way that it doesn't get blocked, e.g. by issuing ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; first... otherwise it just sits and waits, and for a busy database, that could be forever.
    – Aaron Bertrand
    Commented May 9, 2016 at 17:42
  • Setting the database in single_user resolved the issue, I should have guessed... SQLserver always requires single_user for this kind of stuff.
    – A_V
    Commented May 9, 2016 at 17:55

3 Answers 3

Reset to default
21

I would say don't ever use the "Take Offline" thing in the GUI unless you know for a fact that the database is not in use. By anything. That's hard to know without doing some legwork, so why not save this script somewhere and always use this?

USE [master];
GO
ALTER DATABASE $dbname$ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE $dbname$ SET OFFLINE;

And then the reciprocal of course:

ALTER DATABASE $dbname$ SET ONLINE;
GO
ALTER DATABASE $dbname$ SET MULTI_USER;

The reason you need to set it to SINGLE_USER first is to kick out any existing users (there is an option to do so on the detach dialog, but not the take offline dialog), since SQL Server needs exclusive access to the database in order to take it offline. Now, you may want to do some extra legwork anyway to see who is currently using the database, as if you do this in the middle of a large backup operation or ETL job or what have you, that might be problematic.

EDIT : I have filed a suggestion on Connect about this (see Connect #2687832) and have also posted it to Trello (filed under "Object Explorer").

This was fixed in SSMS 16.3; there is now a checkbox that asks if you want to forcefully terminate any active connections.

Improve this answer
2
  • And what if setting single user also fails because it cannot obtain a lock on the database?
    – StackOverthrow
    Commented Mar 19, 2020 at 19:54
  • @user560822 You will have to wait until you can get a lock on the database. If that means you have to start killing sessions...
    – Aaron Bertrand
    Commented Mar 19, 2020 at 19:56
14

When you're already in the hanging situation, don't forget you can search open connections across the server with 

sp_who2

in another database, such as master.
Scan the results for any where the database is the one you are trying to take offline.
Note the spid (process id) column value of those rows.

One at a time, run

kill  ##

where "##" is the spid.

Certainly take care to make sure those processes aren't important to leave running... they will be terminated without even finishing the latest command and without warning.

Improve this answer
2
  • 2
    The accepted answer did not do the trick for me. Failed on the first command. This answer works perfectly. Thank you
    – Cheburek
    Commented Oct 16, 2019 at 13:34
  • that's good :) Yes, the accepted answer is really for avoiding the issue... if you're already hung, you need something like this to kill it
    – Mike M
    Commented Oct 17, 2019 at 15:34
1

If other answers do not help try to kill management studio process from task manager. Did the trick for me once. I guess it helped by dropping open sessions. Also good news is that after restarting the management studio it recovered all opened files. Use this as last resort of course.

Improve this answer
1
  • 2
    This action would only work if the open session blocking the process was one of your own. If it was someone else's connected to the database, it wouldn't help.
    – Ronaldo
    Commented Jun 26, 2020 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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