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

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




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.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |