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

Question

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?

Answer 1

Try below code.

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

For deep discussion see this answer.

Answer 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?