How to drop a SQL Server database and closing all existing connexions

Posted on: 2012-03-08

Executing DROP [YourDatabaseName] to drop a database won't work if previous connection are already made. You need to alter the database to set it to single user and rollback every other connection. Once dropped, you can create back your database. When creating the database, the default value is to set to multi_user so you do not have to worry about setting the value to single_user with the alter.

 IF EXISTS (SELECT name FROM sys.databases WHERE name = N'YOURDATABASENAME') 
  alter database YOURDATABASENAME 
  set single_user with rollback immediate 
  DROP DATABASE YOURDATABASENAME 
  GO 

If you do not do the alter statement, you may have the error : "The database could not be exclusively locked to perform the operation."