SQL SERVER – ERROR – FIX – Msg 3702, Level 16, State 3, Line 1 Cannot drop database “MyDBName” because it is currently in use

During presentations I often create and drop various databases for demonstrations purpose. Recently in one of the presentations, I tried to remove my recently created database, I got following error.

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

The reason was very simple as my database was in use by another session or window. I had option that I should go and find open session and close it right away; later followed by dropping the database. As I was in rush I quickly wrote down following code and I was able to successfully drop the database.

USE MASTER
GO
ALTER DATABASE MyDBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MyDBName
GO

Please note that I am doing all this on my demonstrations, do not run above code on production without proper approvals and supervisions.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info