SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

How to Drop Database in SQL Server by Closing Existing Connections

This article outlines the steps to Drop Database in SQL Server when the users are connected to a SQL Server Database. You may need to closing existing connections in a database before restoring the database, before detaching database, get database in SINGLE_USER mode etc.

If you try dropping a database when users are connected to the SQL Server Database you will receive the below mentioned error message.

Error Message

Drop failed for Database 'MyTechMantra'. (Microsoft.SqlServer.Smo)
Cannot drop database "" because it is currently in use. (Microsoft SQL Server, Error: 3702)

The reason why you will end up getting the above mentioned error is when SQL Server is Unable to Get Exclusive Access to SQL Server Database.

Different Ways to Get Exclusive Access to Drop Database in SQL Server

  • Drop Database in SQL Server Using SQL Server Management Studio (SSMS)
  • Drop Database in SQL Server Using TSQL Query

Drop Database in SQL Server Using SQL Server Management Studio

Step 1: Connect to SQL Server Management Studio; expand Database Node → Right click the Databases which you want to Drop → Select Delete from the drop down menu to open up Delete Object dialog box as shown in the snippet below.

Step 2: Select the Check box “Close existing connections” to Drop Existing Connections before Dropping the SQL Server Database and click OK to Drop Database in SQL Server. By selecting “Delete backup and restore history information for databases” option you will be able to remove the database backup and restore history which is stored in MSDB system database.

Drop Database in SQL Server Using TSQL Query

Execute the below TSQL code to Drop Database in SQL Server Using TSQL Query.

/* Delete Database Backup and Restore History from MSDB System Database */

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyTechMantra'
GO

/* Query to Get Exclusive Access of SQL Server Database before Dropping the Database  */

USE [master]
GO 
ALTER DATABASE [MyTechMantra] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

/* Query to Drop Database in SQL Server  */

DROP DATABASE [MyTechMantra]
GO

Conclusion

In the article you have seen different ways by which you can drop database in SQL Server by getting exclusive access to SQL Server Database.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips