Ошибка 3702 sql server

Description

In this article, I am going to give Fix/Solution for the error ‘Cannot drop database because it is currently in use’ in MS SQL Server.. This error occurs when we try Delete or Drop database while the database connection is used by other users or other resources. So we need to close existing connections first then we need to Drop or Delete the database.

Summary

• Fix/Solution: Cannot drop database because it is currently in use in MS SQL Server in Script
• C# Fix/Solution: Cannot drop database because it is currently in use in MS SQL Server 
• Fix/Solution in Management Studio: Cannot drop database because it is currently in use in MS SQL Server 

USE [MorganDB]
GO
/****** Object:  Database [MorganDB]    Script Date: 11/29/2013 13:29:16 ******/
DROP DATABASE [MorganDB]
GO

USE [master]
GO
/****** Object:  Database [MorganDB]    Script Date: 11/29/2013 13:29:16 ******/
DROP DATABASE [MorganDB]
GO

USE [master]
GO
ALTER DATABASE [MorganDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object:  Database [MorganDB]    Script Date: 11/29/2013 13:40:36 ******/
DROP DATABASE [MorganDB]
GO

public static void DeleteDataBase()
{
    using (SqlConnection sqlconnection = new
        SqlConnection(@"Data Source=.sqlexpress;Initial Catalog=master;Integrated Security=SSPI;"))
    {
        sqlconnection.Open();
        // if you used master db as Initial Catalog, there is no need to change database
        sqlconnection.ChangeDatabase("master");

        string rollbackCommand = @"ALTER DATABASE [MorganDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";

        SqlCommand deletecommand = new SqlCommand(rollbackCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();

        string deleteCommand = @"DROP DATABASE [MorganDB]";

        deletecommand = new SqlCommand(deleteCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();
    }
}

Thanks,
Morgan
Software Developer

How to fix the SQL Server error Cannot drop the database because it is currently in use?

Also know as the 3702 error, the SQL Server error Cannot drop the database because it is currently in use is frequent in multi-user environments. It is possible to manage SQL databases with scripts or via the various windows of SQL Server Management Studio. For example, you can simply delete a database with a SQL DROP statement. However, if there are active connections on the database being deleted, then the database returns an error message, because a database with an active connection cannot be deleted.

1. Scripts that generates the drop database error

The exact error message text is the following:

Cannot drop database because it is currently in use. (Microsoft SQL Server, Error: 3702).

Note: Be careful to check the database backups before deleting it completely. Especially in important projects and production environments.

For example, this type of script generates the error:

USE [master];
GO

DROP DATABASE [DB1];
GO

Another way to create the database is to check if the DB exist on the server, before running the Drop Database command, like this for example:

USE [master];
GO

IF EXISTS (
	SELECT name 
	FROM master.dbo.sysdatabases
	WHERE name = 'DB1'
)
DROP DATABASE [DB1];
GO

To avoid this error, close all active connections to the database before the drop and terminate the current queries. Then close the tabs in SSMS or explicitly end the open connections on the database. Finally close the active tabs if only one user is currently connected. For the second step, run these two operations :

2. Execute sp_who2 procedure to identify IDs of active sessions

In the screenshot we identify the active sessions for the DB1 database. We see one user with SPID 51.

sp_who2

Check out this other article about how to create a database with default options with the SSMS graphic user interface.

3. Close sessions with the SQL Server kill command and SPID

It is the active SPID that prevent the drop from being successful. Indeed, the Microsoft RDBMS do not allow to drop a database with active sessions. Indeed, you need to terminate all the sessions with the SPIDs found. To do this, use the Server Process ID (SPID) found in the previous query to kill the session. Execute the query in SSMS.

kill 51

4. Execute again the drop database script without error

Repeat the operation till no active connections are visible on the list. To go further, here is the official documentation of the T-SQL kill command. The “unable to drop the database because it is currently in use” sql command error is a classical one. Indeed it’s an object that allows many connections from different users at the same time.

Conclusion on SQL Server error 3702

This MS SQL tutorial explains how to avoid the common SQL Server error message : Cannot drop the database because it is currently in use. It is a common error and the tip is very useful when creating and deleting multiple databases in development and testing environments for example.

SQL Server administration tutorials

To go further and learn more on MS databases management, check out this tutorials on the SQL Server DBA topic.

• Empty the SQL Server transaction log and fix error 9002
• How to install a second SQL Server instance on a server?
• How to display the SQL Server detailed version with a query?