Drop a Database by Closing Existing Connections using SSMS or T-SQL

By Dinesh Asanka

To drop a SQL Server database, you will need exclusive access to the database ensure there are no other current users of the database or you will encounter the error:
Drop failed for Database ‘dbName’ …. Cannot drop database because it is currently in use
Ensuring there are no other current users can be very difficult – there may be jobs running using the database or there could be idle users who have opened the connections to the database and so on.
Therefore, you need to find all the spids and kill them. In SSMS when using the UI to drop the database there is an option to Close existing connections:

Alternatively, this can be done using the T-SQL script below.

USE master
Go
ALTER DATABASE [ClusterKey] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ClusterKey

As you can see, first the database will be set to single user mode and point to remember is all the existing connections transactions will be rolled back.