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

SQL SERVER – FIX – Error 3702, Level 16, State 3 - Cannot Drop Database "DB_Name" Because it is Currently in Use kill-01

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.

  1. 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://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Basic Statistics Maintenance – Notes from the Field #083
Next Post
SQL SERVER – FIX: Msg 3102, Level 16, State 1 – RESTORE cannot process database ‘Name’ because it is in use by this session

Related Posts

7 Comments. Leave new

  • Short approach can be risky. I have seen, momentarily new single connection comes and hold your DB. Then you will have no choice left other then stopping the sql server agent. To avoid this I would take the DB in emergency mode. That will help to drop any phantom connection and as a admin then its upto me if I want drop the DB now or I can bring the DB to multi user mode and drop there.

    Reply
  • Short approach can be dengerous.
    I would take the DB emergency to drop phantom connection

    Reply
  • Muhammad Kashif
    June 5, 2015 11:59 am

    Hi,
    You can also use this method, In Management studio right click the database and click delete, delete object form will be opened check “Close existing connections” and OK. It will close all connections and delete database.

    Reply
    • Muhammad – Yes. If you look at the command that checkbox “Close existing connections” generates – its the same.

      Reply
  • digitaltafur
    June 6, 2015 1:14 am

    One problem with method #1 is when a user is pointing to another DB and running a query on the DB you are trying to Drop. I haven’t been able to find a good way to kill these type of connections since sysprocess, sp_who2 and sys.dm_exec_sessions will all point to the incorrect DB.

    Reply
    • Yeah. That’s why I said its long approach. If you are on busy server, you may have to do same steps multiple times.

      Reply

Leave a Reply

Menu