Error 5070 when deleting Microsoft SQL Server database

Question

I have recently relocated a database and need to delete any existence of the old database. Since I want to completely remove it, I am simply opening up SQL Server Enterprise Manager, finding the database and trying to delete it. However I am getting the following error:

Error 5070: Database state cannot be changed while other users are using the database. ALTER DATABASE statement failed. sp_dboption command failed.

I don't care about the users or whatever is stopping me from deleting this database. I just need to get rid of it. Please help me figure out how to complete this task.

Answer 1

You have users connected to the database that need to be disconnected. See who they are using sp_who. More info on MSDN concerning sp_who. To disconnect users, you can use a script such as this one.

You could also forcibly disconnect all users if you're sure that won't cause a problem with the following:

ALTER DATABASE alerts SET OFFLINE ROLLBACK IMMEDIATELY;

That rolls back open transactions, which is graceful, but it's still a bit scorched earth since you won't know who was connected. I think it would be more productive to see who's connected using sp_who so that you know who you need to change connection strings for so they won't continually access the old server.