The SQL Server Error “Drop failed for Database” has some additional information that “An exception occurred while executing a Transact-SQL statement or batch.“, we can also see the namespace “Microsoft.SqlServer.ConnectionInfo” in the additional information.
In the additional information, we can see the important messages, which can help us to solve the issue. The important information is “Cannot drop database, because it is currently in use” with the error code “Microsoft.SqlServer, Error: 3702“.
Problem
When the user tries to delete a database, if the database is being used anywhere for example in any application, then the issue Drop failed for Database will be popped. As the connection is open and active, Microsoft SQL Server will not allow directly to delete the database.
Solution
With the problem statement itself, we can guess the solution. The solution should be closing the existing opened connection. Finding where it’s opened and closing it is a hectic task I know. The easiest solution is to select the Close existing connections option before hitting the Ok button in the Delete Object dialog box. Generally, this option will be not selected by default for the safer side.
Most of the people will not notice this option while deleting a database, including me. After checking carefully, I came to know this option.
Conclusion
A lot of people will not observe this option where can delete the database with the option Close existing connections. I just encountered the problem as well as the solution, so sharing it with others to minimize this kind of silly observation.