Dropping an Active Database in SQL Server

How do you drop a database in SQL Server? Easy, right?

DROP DATABASE SomeDatabase

This won’t work if you have active connections to your database. You might have an application connecting, or even Management Studio or some other tool holding an open connection.

But what if you really, really want to drop the database? Maybe you’re going to recreate it, or reseed it, and you need to kill all existing connections.

The answer is perplexingly simple:


ALTER DATABASE SomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DROP DATABASE SomeDatabase

This immediately disconnects all users, closes any open connections, rolls back any transactions in progress, and drops the database.

Dangerous? Yes. But if you’re trying to drop the database, you should already be aware of the dangers of doing so.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Tools and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *