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.
Table of Content
Error 3702 – SQL Server Can’t Drop Database in Use
This 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.
Important Tip – SQL Error 3702
Apart from the mentioned reasons, there are times, when users’ database is corrupted. Evidently, this causes several technical errors. In order to get rid of such corruption issues, users can try the SQL Database Recovery Tool to fix the issue. This software can easily repair the damaged MDF files without any errors.
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 DROP command command mentioned above. This time, users will most likely get the desired solution.
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, they can email us.