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
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.




Related Articles :

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 |