Sql-server – How to force a drop of MSSQL Server database

sql server

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.

Best Answer

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
Related Topic