Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.