How to Fix “Cannot Drop Database Because It Is Currently in Use” in SQL Server

Sandhiya Priya
8h
101
0
0

Article

When working with SQL Server, you may encounter the following error while trying to delete a database:

Msg 3702, Level 16, State 3
Cannot drop database "DATAFEED" because it is currently in use.

This error occurs because SQL Server does not allow a database to be dropped while active connections are using it.

In this article, we will understand:

Why this error occurs
How to fix it properly
Safe methods to drop a database
Best practices to avoid this issue

Why This Error Happens

A database cannot be dropped if:

A query window is currently connected to it
Another user is accessing it
An application is using it
A background process or job is connected

Even your own SSMS query window can cause this error if it is set to use that database.

Step-by-Step Solution to Drop the Database

Step 1: Switch to Master Database

Before dropping the database, you must switch context to another database (usually master):

USE master;
GO

Step 2: Set Database to Single User Mode

This forces SQL Server to disconnect all active users.

ALTER DATABASE DATAFEED
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Explanation:

SINGLE_USER → Allows only one connection
WITH ROLLBACK IMMEDIATE → Terminates all active sessions immediately

Step 3: Drop the Database

DROP DATABASE DATAFEED;
GO

Complete Safe Script

Here is the recommended full script:

USE master;
GO

ALTER DATABASE DATAFEED
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE DATAFEED;
GO

This method works in most situations.

Safer Version (Drop Only If Database Exists)

To avoid errors if the database does not exist:

USE master;
GO

IF DB_ID('DATAFEED') IS NOT NULL
BEGIN
    ALTER DATABASE DATAFEED 
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    
    DROP DATABASE DATAFEED;
END
GO

How to Check Who Is Using the Database

If you want to see active connections:

SELECT *
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('DATAFEED');

This shows active sessions connected to the database.

Important Warnings

Dropping a database:

Deletes all tables
Deletes all stored procedures
Deletes all data
Cannot be undone

Always take a backup before dropping:

BACKUP DATABASE DATAFEED
TO DISK = 'C:\Backup\DATAFEED.bak';

Best Practices

Always switch to master before dropping

Ensure no active application is connected

Use SINGLE_USER WITH ROLLBACK IMMEDIATE carefully

Take a backup before deletion

Use the IF EXISTS condition in production

Conclusion

The error “Cannot drop database because it is currently in use” is common in SQL Server. It happens when active connections are using the database.

By switching to the master database and setting the target database to SINGLE_USER mode with ROLLBACK IMMEDIATE, you can safely remove it.

Understanding database connections and proper deletion procedures is essential for safe database management.