SQL Server SSMS – Handling Stuck Tasks

sql serverssms

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 ?

Best Answer

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.

Related Question