SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use – Part 2

Following error is very generic error and I have previously written SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use.

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

One of the reader Dave have posted additional information in comments. I will list his advise here. First read the original post here.

If you are still getting the error after you try using
USE master
GO
DROP DATABASE (databaseName)
GO

Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
USE master
GO
DROP DATABASE (databaseName)
GO

Why You Still See Cannot Drop Database Because It Is in Use

Closing Management Studio works because it closes every connection that SSMS opened for you. That includes query windows you forgot about and Object Explorer. Any of them can hold a session in the database, and one session is enough to block the drop. Even a query window that finished its work long ago keeps its connection open until you close it.

Restarting SSMS is a big hammer, though, and it does nothing for connections from other machines. A few quicker checks:

• Look at the tabs in your own SSMS. Each query window shows the database it is connected to in the toolbar and in the status bar.
• Run sp_who2, or query sys.sysprocesses filtered on dbid = DB_ID('DataBaseName'), to see every session in that database with its host and program name.
• Check the default database of your login. If it points to the database you want to drop, every new connection lands there. Change it with ALTER LOGIN YourLogin WITH DEFAULT_DATABASE = master.

If a session belongs to an application or a job, stop it at the source first. Otherwise it may reconnect a second after you end it. You can end a single session with KILL and its session ID, and any open transaction in it rolls back.

When nothing else works on a test server, ALTER DATABASE DataBaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE in the same batch as the drop removes everyone at once. Save that for databases you are sure nobody needs.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.