8

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?

Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438

3 Answers3

30

Try below code.

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

For deep discussion see this answer.

Community
  • 1
  • 1
Kaushik Maheta
  • 1,741
  • 1
  • 18
  • 27
  • Works great in the restore scenario from bacpac. Ideal for dev environement, but I wouldn't do that in production – Jurion Sep 27 '20 at 17:38
2

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?

Community
  • 1
  • 1
japzdivino
  • 1,736
  • 3
  • 17
  • 25
  • What if I'm dropping the DB and **totally recreating** it? Do I still need to set it to multi user? Or is that the default for the new databases? – Konrad Viltersten Nov 24 '15 at 09:57
  • @KonradViltersten That is a default if the database is newly created no need to set, all you have to do is to manage your database users. – japzdivino Nov 24 '15 at 09:59
0

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

Community
  • 1
  • 1
t1t1an0
  • 281
  • 1
  • 16