SQL SERVER – FIX – Error 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use

We learn from mistakes and we improvise with experience. I couldn’t think of a better example than the one below for this. I have been watching many sessions by various speakers and found that many times they have struggled to get rid of  Error 3702.

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “DemoDB” because it is currently in use.

The meaning of the error message is pretty clear that someone is using the database and it can’t be deleted/dropped. First, make sure that it is not our own connection. To make sure, always change the context before dropping the database as shown below

USE MASTER
GO
DROP DATABASE DemoDB
GO

If we are still getting above error, then there are two approaches to solve this:

1. Long approach: Find the connections which are blocking me to drop the database. Essentially find those sessions which are using this database and then kill those sessions. Here is the script to generate kill command.

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

This would give output as below.

We can run the kill command and try dropping database again. If it was successful, go ahead else repeat the loop.

2. Short approach: SQL Server has inbuilt functionality where it can kick out all the connections. There is a cause called “WITH ROLLLBACK IMMEDIATE” which can be used to kill other connections and forcing rollback of their work.

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

If you want to learn other rollback options available with ALTER DATABASE, you can read my earlier blog as well

SQL SERVER – Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE

Hope this helps and you will use the new technique in your presentations.

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)