11

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 ?

  • 4
    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 May 9 '16 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 May 9 '16 at 17:55
12

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").

4

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.

  • 1
    The accepted answer did not do the trick for me. Failed on the first command. This answer works perfectly. Thank you – Cheburek Oct 16 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 Oct 17 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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