SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

Drop Database in SQL Server by Killing Existing Connections

Introduction

Let’s learn how to Drop Database in SQL Server when the users are connected to the SQL Server Database.

You may find the need to close all the existing database connections in a database before restoring the database, before detaching the database and for this, you need to get the database in SINGLE_USER mode.

If you ever try to drop a database when users are connected to the SQL Server Database then 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)
Cannot drop database because it is currently in use Microsoft SQL Server Error 3702 

The reason why you 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

  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.
Close Existing Connection in an SQL Server Database before Restoring the Database
2.

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.

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

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Add comment

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

Manning