Home / server / Questions / 317126
In Process
GMitch
Asked: 2011-09-30 21:44:38 +0800 CST

Error 5070 when deleting Microsoft SQL Server database

  • 772

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.

sql-server database

1 Answers

  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.

    • 2