Cannot Drop Database Because It Is Currently in Use (Error 3702)

🚨Part of the SQL Server Errors series, the exact messages and what actually causes them.Related pillar: Blocking & Locking

Msg 3702 means a session is still connected to the database you are dropping. Find it, and check it is not your own, before you clear anything.


What Msg 3702 Says

Msg 3702  ·  Level 16  ·  State 4
Cannot drop database "YourDb" because it is currently in use.
⚡Run it from master, and if something else is still connected, put the database in single-user mode and drop it in the same batch. Find who is in it first, because clearing them rolls back whatever they had open.

DROP DATABASE needs every other connection out, and it does not wait for them: it fails straight away with 3702. The state number says whose connection it was. Measured on SQL Server 2025, State 3 was the session running the DROP itself, its own query window still sitting in the database, and State 4 was a different session. So check the state before you go looking for somebody else.


Find the Sessions Holding It

SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE s.database_id = DB_ID(N'YourDb')
  AND s.session_id <> @@SPID
ORDER BY s.last_request_end_time;

An SSMS program_name is usually a query window or Object Explorer, and an application name is a connection pool that will reconnect the moment you clear it. Get Active Connections by Database gives the same answer for every database at once.


Your Own Window Counts

If the query window running the drop has the database as its context, the drop fails with State 3. Switch to master in the same batch:

USE master;
DROP DATABASE YourDb;
Msg 3702 State 3 in SSMS: DROP DATABASE fails because the same window is still using the database

In SSMS, the Delete dialog has a Close existing connections checkbox at the bottom. It clears the other sessions for you, with the same rollback as the next section, so read the session list before you tick it.


Clear Them and Drop in One Batch

USE master;
ALTER DATABASE YourDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE YourDb;

WITH ROLLBACK IMMEDIATE rolls back every open transaction in the database instead of waiting. In the lab run it printed Nonqualified transactions are being rolled back. Estimated rollback completion: 100%., and the session it threw out, which was mid-transaction, got Cannot continue the execution because the session is in the kill state. That is somebody else’s work being undone, so this belongs in a change window.

Keep the two statements together. Run them separately and an application pool can take the single connection in between, which is the lockout The Database Is in Single-User Mode gets you out of. Kill All User Sessions on a Database has this whole run captured on SQL Server 2025, from the State 4 error and the session that held it through to the clean drop, and the one KILL at a time route for when you would rather not change the database at all.


Do Not Take It Offline First

Setting the database OFFLINE also clears the sessions, and the drop then succeeds. It leaves the data and log files on disk. Microsoft’s DROP DATABASE reference says so in one line, and it bites later: creating a database with the same name failed in the lab with

Msg 5170  ·  Level 16  ·  State 4
Cannot create file 'C:\...\DATA\YourDb.mdf' because it already exists. Change the file path or the file name, and retry the operation.

and attaching the leftover files back failed with operating system error 5 (access denied), so they were not even a way back. Drop online, with the single-user batch above, and the files go with the database.

Dropping an offline database leaves its files behind, so CREATE DATABASE fails with Msg 5170

When It Is Not a Session

A database snapshot stops the drop with a different message, and no session list will show it:

Msg 3709  ·  Level 16  ·  State 2
Cannot drop the database while the database snapshot "YourDb_snap" refers to it. Drop that database first.

Drop the snapshot first. Get Database Snapshot Inventory lists every snapshot and its source. Change data capture is not one of these: a CDC-enabled database, with its capture job running, dropped cleanly in the same test, and its capture and cleanup jobs went with it.

Msg 3709: cannot drop the database while a database snapshot refers to it

Frequently Asked Questions

I closed every window and it still says currently in use.
Look at the state. State 3 is your own session, so the window you are running the drop from is still in the database: put USE master; in front of it. State 4 is someone else, and the session query above names them.
Is WITH ROLLBACK IMMEDIATE safe?
It is decisive, not safe. Every open transaction in the database is rolled back and those sessions are thrown out. Run the session list first so you know whose work that is.
Why not just restart SQL Server?
Because every other database on the instance goes down with it, and the application reconnects to this one the moment it is back. The single-user batch does the same job for one database.
Does dropping the database delete the files?
Yes when it is online. No when it was offline at the time of the drop: the .mdf and .ldf stay where they were, and a new database with the same name cannot be created on top of them.

Where To Go Next

If the database still will not go, these cover the sessions and the lockouts around it:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *