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:
- 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.
- 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)
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.
Short approach can be dengerous.
I would take the DB emergency to drop phantom connection
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.
Muhammad – Yes. If you look at the command that checkbox “Close existing connections” generates – its the same.
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.
Yeah. That’s why I said its long approach. If you are on busy server, you may have to do same steps multiple times.