CODEWITHSUNDEEP
sqlsql-server
Konrad Viltersten
Konrad Viltersten

Reputation: 39108

How to drop a database when it's currently in use?

NB. I don't want to mark the check box in the wizard for deletion. This question's strictly about scripting the behavior.

When I run the following script to get a fresh start, I get the error that the database Duck can't be deleted because it's currently in use.

use Master
drop database Duck
drop login WorkerLogin
drop login AdminLogin
go

Be that as it may (even though I'm the only user currently in the system and I run no other queries but that's another story), I need to close all the existing connections. One way is to wait it out or restart the manager. However I'd like to script in that behavior so I can tell the stubborn server to drop the duck down. (Yes, "typo" intended.)

What do I need to add to the dropping statement?

Upvotes: 12

Views: 43621

Answers (3)

Kaushik Maheta
Kaushik Maheta

Reputation: 1891

Try below code.

USE master;
ALTER DATABASE [Duck] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [Duck] ;

For deep discussion see this answer.

Upvotes: 44

japzdivino
japzdivino

Reputation: 1746

You have to kill first all active connections before you can drop the database.

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER

http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database

How do you kill all current connections to a SQL Server 2005 database?

Upvotes: 2

t1t1an0
t1t1an0

Reputation: 281

if you're ssms tab is not currently on the db to be dropped (meaning you are in the master db), then these will help:

https://dba.stackexchange.com/questions/2387/sql-server-cannot-drop-database-dbname-because-it-is-currently-in-use-but-n

https://dba.stackexchange.com/questions/34264/how-to-force-drop-database-in-sql-server-2008

Upvotes: 0

Related Questions