How to Drop a SQL Database That Is Currently in Use

If you’ve ever tried to delete a database in SQL Server and been stopped by the error “Cannot drop database because it is currently in use,” you’ve run into one of SQL Server’s safety features. SQL Server won’t drop a database while any connection is using it, whether that connection is your own query window, an application, a scheduled job, or another user. To drop it, you first have to clear those connections.

This guide explains why the error happens and walks through the safest ways to drop a database that’s in use, using T-SQL or SQL Server Management Studio (SSMS). It also notes how other database systems handle the same situation.

Before You Start: Dropping a Database Is Permanent

Dropping a database deletes it and its data files. There’s no undo. Before you continue:

• Take a backup if there’s any chance you’ll need the data.
• Double-check the database name, especially on a server with similar names like “Sales” and “Sales_Test.”
• Warn users and pause applications that connect to it. The methods below forcibly disconnect active sessions, which rolls back their open transactions.
• Confirm you have permission. You generally need to be the database owner, have CONTROL permission on the database, or be a member of the sysadmin or dbcreator roles.

Why the Error Happens

A very common cause is your own session. If your query window is connected to the database you’re trying to drop (for example, you ran USE MyDatabase earlier, or SSMS opened the window with that database selected), SQL Server sees that as an active connection. Other common causes are applications with connection pools, SQL Server Agent jobs, reporting tools, and other people’s SSMS windows.

Method 1: Set the Database to Single-User Mode, Then Drop It (T-SQL)

This is the most widely used approach. It disconnects everyone and rolls back their open transactions immediately.

Step 1: Switch to the master database

Run the following so your own session isn’t holding the database:

USE master;
GO

Step 2: Force single-user mode

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

WITH ROLLBACK IMMEDIATE tells SQL Server to roll back incomplete transactions and close other connections right away instead of waiting.

Step 3: Drop the database

DROP DATABASE [MyDatabase];
GO

Run Steps 2 and 3 together in the same batch window, back to back. If you wait between them, another application could grab the single available connection first, and you’ll see the error again.

On SQL Server 2016 and later, you can use DROP DATABASE IF EXISTS [MyDatabase]; so the script doesn’t fail if the database has already been removed.

Method 2: Use SQL Server Management Studio

If you prefer a graphical method:

1. In Object Explorer, expand Databases.
2. Right-click the database and choose Delete.
3. In the Delete Object window, check Close existing connections at the bottom.
4. Decide whether to keep Delete backup and restore history information for databases checked.
5. Click OK.

The “Close existing connections” option does essentially the same thing as Method 1 behind the scenes. Close any of your own query windows that are connected to the database first, too.

Method 3: Find and End Specific Sessions

If you’d like to see who is connected before disconnecting them, query the active sessions:

SELECT session_id, login_name, host_name, program_name
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('MyDatabase');

You can also run EXEC sp_who2; and look at the DBName column. To end a specific session, use KILL followed by its session ID, for example KILL 57;. Once no sessions remain, run DROP DATABASE from the master database.

This method is more careful, but connections can reappear quickly if an application keeps reconnecting, which is why Method 1 is usually more reliable.

Method 4: Take the Database Offline First

Another option is to take the database offline, then drop it:

ALTER DATABASE [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE [MyDatabase];

Be aware that when you drop an offline database, SQL Server removes it from the server but doesn’t delete its data and log files from disk. You’ll need to delete those files manually. That can be helpful if you want to keep the files, but it’s easy to forget and leave large files behind.

Troubleshooting

• Still “in use” after single-user mode: Another connection may have taken the single slot. Run the ALTER and DROP statements in one batch, or temporarily stop the application or SQL Server Agent jobs that connect to the database.
• Permission errors: Ask a database administrator with the right role to run the commands.
• Database is part of an availability group or replication: You’ll need to remove it from the availability group or replication setup before dropping it.
• System databases: You can’t drop master, model, msdb, or tempdb.

What About Other Database Systems?

The “currently in use” error is specific to SQL Server, but other systems have similar rules:

• PostgreSQL refuses to drop a database with active connections. PostgreSQL 13 and later support DROP DATABASE mydb WITH (FORCE);, which terminates existing connections first.
• MySQL generally lets you run DROP DATABASE while others are connected, but the statement can wait on locks held by active transactions.

Summary

To drop a SQL Server database that’s in use, switch to master, set the database to single-user mode with ROLLBACK IMMEDIATE, and then run DROP DATABASE right away. In SSMS, check “Close existing connections” in the Delete dialog. Always take a backup first, because dropping a database can’t be undone.

More database guides: how to install MySQL on Windows 11 and how to install MySQL Workbench in Windows 10.

Related: Select distinct on multiple columns in SQL.

Related: Install SQL Server on Windows 10.