CODEWITHSUNDEEP
sqlsql-serverwindows
Shuzheng
Shuzheng

Reputation: 13840

Cannot drop a database in Azure Data Studio, because it's currently in use

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:

Upvotes: 21

Views: 28528

Answers (2)

Gauravsa
Gauravsa

Reputation: 6514

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

Upvotes: 4

Thom A
Thom A

Reputation: 95554

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

Upvotes: 67

Related Questions