15

How do I drop a database which shows DatabaseName (Single User) as its name?

When I try to delete it, I get the following error:

Alter failed for Database 'DatabaseName'. (Microsoft.SqlServer.Smo)

ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5064)

I tried to execute the ALTER below and still have the same issue.

ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT
Mike Walsh
  • 18,173
  • 6
  • 47
  • 72
Prasad Kanaparthi
  • 251
  • 1
  • 2
  • 5

2 Answers2

28

If you are going to drop a database, you have to be the only connection to that database. If any other connections are there, you can't drop it. From the error message (that error means your database is in Single_User mode but there is already a connection so you can't connect) my assumption here is that you tried to set it to Single_User mode and then attempted to do the drop but either you grabbed a connection that you didn't know about, or some other process has. The fact that restarting SSMS worked for you tells me it was probably you grabbing that connection. So here is how you can fix that.

Logically you have to put the database back into multi_user mode so you can then put it into single_user mode again (but this time you'll be in control of that single connection allowed and drop the database before something else connects) and then your database will be gone.

In code here is how you need to do this (but first close your query windows that are connected to that database. Restart SSMS and make sure you don't select this database in the object browser):

-- Then attempt to take your database to multi_user mode, do this from master
USE MASTER 
GO

ALTER DATABASE myDatabaseName 
SET multi_user WITH ROLLBACK IMMEDIATE
GO

-- Now put it into single_user mode and drop it. Use Rollback Immediate to disconnect any sessions and rollback their transactions. Safe since you are about to drop the DB.
ALTER DATABASE myDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE myDatabaseName
GO
Mike Walsh
  • 18,173
  • 6
  • 47
  • 72
  • This solution won't work if the database is already in Single-User mode and you try to access it from an other connection. – Maxim Paukov Dec 13 '12 at 23:16
  • 4
    That's right Maxim - That's why I said that my assumption here based on the information provided and the OPs self answer is actually the one with the open connection probably via object explorer or a query window... If it was open some other way by some other user, then you would have to find that one connection that stole the single connection allowed and then kill -that- one connection's session and follow the steps outlined above.. – Mike Walsh Dec 13 '12 at 23:22
16

If you try to access the database which is already in the Single-User mode, you need to close all the connections to the database first, otherwise you will get an error message:

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'DatabaseName' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

The following query kills the processes accessing the database:

-- Create the sql to kill the active database connections  
declare @execSql varchar(1000), @databaseName varchar(100)  
-- Set the database name for which to kill the connections  
set @databaseName = 'DatabaseName'  

set @execSql = ''   
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '  
from    master.dbo.sysprocesses  
where   db_name(dbid) = @databaseName  
     and  
     DBID <> 0  
     and  
     spid <> @@spid  
exec(@execSql)
GO

Then you should be able to bring the database back to Multi-User mode as usual:

ALTER DATABASE 'DatabaseName' SET MULTI_USER
Maxim Paukov
  • 261
  • 1
  • 4
  • 2
    This is a very, very cumbersome solution and equates to a frustrating game of whack-a-mole on a busy system. To kick all users out, much easier to use ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE as Mike's answer shows. – Aaron Bertrand Dec 13 '12 at 13:33
  • 2
    @AaronBertrand Mike's solution will not work if the database is already in Single-User mode and you try to access it from an other connection. – Maxim Paukov Dec 13 '12 at 23:12
  • 2
    Right, if that is the case, he would have to find the sessions as you have outlined. However, if the connection that set the database to single_user is his own... – Aaron Bertrand Dec 14 '12 at 00:09