5

I am trying to delete an MSSQL Server database, however I am having no luck. I have tried multiple things such as

user
ALTER DATABASE my_database
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE my_database;
GO

I have also tried to right click on it a delete it.

This does not work, it tells me "Cannot drop database "ima_debts" because it is currently in use". The thing is there is definately no other user connected to it. In fact I disabled TCP/IP for the database and restarted it.

Not even "Microsoft SQL Server Management Studio (Administrator)" is connected to it. I have made sure to login to "master".

Why is it telling me it is currently in use. Is it possible for me to delete perhaps a directory or something from the file system to get rid of this database?

Any help would be appreciated.

Thanks.

3 Answers 3

5

Sometimes IntelliSense and/or Management Studio will hang connections open just because you clicked on the database in Object Explorer. Also, if you have any SQL Agent jobs that access the database, these will not be stopped by disabling TCP/IP.

Regardless, the following script will "take over" the database by putting it into single-user mode in the session, and then take it offline so no one can connect to it to run queries.

You can then drop the database using Management Studio or T-SQL.

USE [MyDB];
GO

ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [MyDB] SET OFFLINE;
GO
1
  • 1
    Don't you need to use db 'master' here? Jul 10, 2015 at 9:58
2

From http://www.morgantechspace.com/2013/11/Cannot-drop-database-because-it-is-currently-in-use-in-MS-SQL-Server.html

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

In C#

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 [TestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";

        SqlCommand deletecommand = new SqlCommand(rollbackCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();

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

        deletecommand = new SqlCommand(deleteCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();
    }
}
0

When you delete it in SSMS there's a checkbox that reads "Close existing connections". This checkbox is not checked by default. Check the box and any connections to the DB will be terminated allowing you to delete it.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .