Database currently in use-

 

 

Msg 3702, Level 16, State 3, Line 2
Cannot drop/detach database “DataBaseName” because it is currently in use.
The common mistake user is kept the connection open with this database and trying to drop the database.

 

 

The following commands will raise above error:
USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO


Fix/Workaround/Solution:
The following commands will not raise an error and successfully drop the database:
USE Master;
GO
DROP DATABASE AdventureWorks;
GO


If you want to drop the database use master database first and then drop the database.

If you are still getting same error that means the connections are open from applications. So we nee to close other connections first 

 

 

USE [Master];
GO
DECLARE @dbname sysname
SET @dbname = 'AdventureWorks'

IF EXISTS (SELECT name FROM sys.databases WHERE name = @dbname)
BEGIN
PRINT N'Database already exists';
END

DECLARE @spid int
SELECT spid  from master.dbo.sysprocesses where dbid = db_id(@dbname)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
SELECT @spid
WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE
('KILL ' + @spid)
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
    END
Print ' All Connections Closed'

GO
DROP DATABASE
AdventureWorks;
GO