As SQL Server users, we often come across various error messages that can be quite frustrating to deal with. One such error is Error 3702, which occurs when we try to drop a database that is currently in use. In this article, we will explore this error in detail and discuss two approaches to resolve it.

The Error Message

The error message, “Msg 3702, Level 16, State 3, Line 1 Cannot drop database ‘DemoDB’ because it is currently in use,” clearly indicates that the database cannot be dropped due to active connections.

Before attempting to drop the database, it is important to ensure that our own connection is not using it. To do this, we can change the context to the master database using the following command:

USE MASTER
GO

Once we have changed the context, we can proceed with dropping the database:

DROP DATABASE DemoDB
GO

If we still encounter the same error, there are two approaches we can take to resolve it.

Approach 1: Long Approach

The long approach involves identifying the connections that are blocking the database from being dropped. We need to find the sessions that are currently using the database and kill those sessions. Here is a script that generates the kill command:

SELECT 'kill ' + CONVERT(VARCHAR(100), session_id)
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('DemoDB')
AND session_id <> @@spid

Running this script will provide us with a list of kill commands. We can execute these commands to terminate the sessions and then attempt to drop the database again. If the database is successfully dropped, we can proceed; otherwise, we may need to repeat the process.

Approach 2: Short Approach

SQL Server provides an inbuilt functionality to forcefully terminate all connections to a database. This can be achieved using the “WITH ROLLBACK IMMEDIATE” clause. Here is an example:

USE [master]
GO
ALTER DATABASE [DemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [DemoDB]
GO

By setting the database to single-user mode with the “WITH ROLLBACK IMMEDIATE” clause, all other connections will be terminated, and their work will be rolled back. We can then proceed to drop the database without any issues.

If you are interested in learning about other rollback options available with the ALTER DATABASE statement, you can refer to my earlier blog post on the topic: “SQL SERVER – Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE.”

We hope this article has provided you with a better understanding of Error 3702 in SQL Server and the approaches to resolve it. By implementing these techniques, you can effectively handle this error and avoid any unnecessary roadblocks in your SQL Server operations.