21

I cannot drop custom databases in Azure Data Studio, because they are currently in use.

I've been looking for various ways to close the zap database, but I cannot find any in the UI.

Only by restarting Azure Data Studio, is the zap database in an "Auto Closed" state, which lets me drop it using:

drop database zap;

How do I close a connection to a database without restarting Azure Data Studio?

Open:

Auto-closed:

2
  • 2
    Try USE master;ALTER DATABASE zap SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE zap;
    – Dan Guzman
    Commented Jan 2, 2020 at 12:35
  • 1
    that didnt work
    – itye1970
    Commented Aug 1, 2022 at 8:58

2 Answers 2

Reset to default
66

Someone else is connected, so you need to set it to SINGLE_USER. This will terminate any existing connections, so that the database can be dropped.

USE master;
GO

ALTER DATABASE zap SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE zap;
GO
7
  • 1
    If someone could advise why this is unuseful, that would be great.
    – Thom A
    Commented Jan 2, 2020 at 12:37
  • Thanks. Can you explain what your query does? Also how can someone else be connected, when I'm the only user?
    – Shuzheng
    Commented Jan 2, 2020 at 12:53
  • I explain that in the opening statement, @Shuzheng : "This will terminate any existing connections, so that the database can be dropped." It might be an Agent job, another query window, etc. Looking at your first image, looks like you have the management page open for the database zap; that will be at least one (other) connection there.
    – Thom A
    Commented Jan 2, 2020 at 12:55
  • Ohh, so each tab (page) makes a separate connection to the database? I thought that connections only exist for the DBMS (SqlServer), and not to the individual databases - am I wrong? Also, why is WITH ROLLBACK IMMEDIATE necessary in the query?
    – Shuzheng
    Commented Jan 3, 2020 at 9:26
  • No, if you have 7 "tabs" open, and are connected to the server in each of them, you have 7 connections. Those Connections with be what ever database you have in "use" on those tabs (and what database you are connected to is connection dependant).
    – Thom A
    Commented Jan 3, 2020 at 9:41
4

You can try the following:

use master;
go
ALTER DATABASE zap SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Go

then drop database.

There is no need to find session which is connected to your database. it can be as simple as an object explorer open in another window. kill the session with the command above and then drop the database.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode?view=sql-server-ver15

6
  • 3
    I was writting my answer at the same time as yours. There is no need for that downvote... If I put that answer there 30 minutes later, yes, but my answer actually explains the code so took longer to write; yours does not. (Yes, I know the downvote was yours, as your reputation lowered by one after a refresh).
    – Thom A
    Commented Jan 2, 2020 at 12:38
  • Thanks! Is it normal that you need to use such queries to drop a database? Also, can you explain what your query does?
    – Shuzheng
    Commented Jan 2, 2020 at 12:51
  • basically, if other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning. this is as per learn.microsoft.com/en-us/sql/relational-databases/databases/…
    – Gauravsa
    Commented Jan 2, 2020 at 12:52
  • so, if any sessions/users are connected to the database, they will be 'zapped' off.
    – Gauravsa
    Commented Jan 2, 2020 at 12:53
  • Thanks. Do multiple connections from the same user also count as separate connections, so that I cannot drop the database, if I have several connections? Could you explain why WITH ROLLBACK IMMEDIATE is necessary?
    – Shuzheng
    Commented Jan 3, 2020 at 9:28

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