SQL Server Can’t Drop Database in Use – Fix Error 3702

Synopsis: In this article, we’re going to fix the SQL Server Can’t Drop Database in Use error. This 3702 error is quite common for SQL users and is not that tough to fix. However, new users who are not proficient in SQL technicalities might still face some issues. Therefore, this article is going to explain the best ways for users to fix this error.

Moreover, users will learn the causes of this error, several important tips & the combined knowledge that the users require for the same. Let’s quickly understand the entire error first, so that we can move ahead to its subsequent topics.

Error 3702 – SQL Server Can’t Drop Database in Use

This cannot drop DB because it is currently in use error occurs in the database, whenever a user tries to drop their database. However, it’s not as simple as it looks like. This error only occurs when users are having an active connection with a database. Putting it simply, when a user with an active database connection, tries to drop that database, faces error 3702.

It’s mostly observed in the multi-user environments of SQL Server databases. Although users have SSMS  & T-SQL commands to do so, this task can still be hampered by small mistakes like the one stated above. Therefore, we have two solutions for this. Both are mentioned below.

Before we jump on to the solutions, users must back up their SQL database. This way, it’ll be easier for them to just safely DROP the database.

Cannot Drop Database Because It Is Currently in Use – All Error Reasons

There are a few reasons why users face this error while executing the MSSQL drop database task or any other. If users can understand these causes from their roots, solving the entire problem will be as easy as fixing LEGO blocks. Thus, below we have mentioned the most common causes:

Active Connections in the Database: Now, we have the most common cause here which is to attempt a database drop operation with an active transaction going on. Any active transaction, stored procedure, or even users with connected instances might create this problem.

Sudden Query Termination: Abrupt system shutdowns during any ongoing execution of a query often end with severe data abnormalities. Moreover,  various SQL errors can originate because of this like the 3702 error in the SQL server & many more.

Corruption Issues In DB: Database files when catching corruption, are useless. It’s just like iron with rust. We can easily fix this corruption only if we have the right tool for it (mentioned in further sections).

Easy Fix for SQL Server Cannot Drop Database Error

Most of the time users execute the below command:

USE Database_Test;
GO
DROP DATABASE Database_Test;
GO

However, if the connection is active, users will face the error 3702: Cannot drop the database because it is currently in use issue. Therefore they should run the below-mentioned command: 

USE Master;
GO
DROP DATABASE Database_Test;
GO

In case, users still face this error even after executing this then they have to go through the detailed solution we are going to discuss next.

Fix Cannot Drop the Database Because It Is Currently in Use (Detailed)

Run Execute sp_who2 Procedure

Now, users need to run the sp_who2 Procedure in order to identify the active sessions in the database.

Close Active Sessions with KILL Command & SPID

Now, that users have the active SPID, they just need to use the KILL command for that active id.

Again run the T-SQL DROP database command mentioned above. This time, users will most likely get the desired solution & solve this cannot drop database because it is currently in use issue.

Bringing It All Together

Finally. Users are aware of the right method to get the perfect solution here. However, they must make sure that they have a backup of the database in case anything goes wrong. Both the solutions mentioned above can easily fix the SQL Server can’t drop database in use 3702 error. However, the automated tool is way better as it can fix nonclustered index in SQL Server, damaged MDF file, etc. Rest, if users do have any more queries related to this cannot drop DB because it is currently in use error, they can email us.