SQL Server 2008 R2 – How to Drop Database Stuck in Single User Mode

sql-server-2008-r2

I've got a database in single user mode that I can't get out of single user mode. I've tried resetting the server, and I've even tried resetting my computer but with every attempt I make to do anything to it SQL Server insists that there is some connection already open to it somewhere.

I don't really understand how it's possible for there to be a connection open to it after I've just reset my computer and have not opened a connection to it. I am using SSMS and I made a connection to the server using the object explorer after which I tried to delete the database from there directly by using the delete key (i.e. I don't have any query tabs open so definitely no connections there).

Unfortunately, the application I'm using doesn't allow me to specify a database name which means I've got to figure out how to get rid of this stupid database so I can restore another one.

I've been googling and trying different things but nothing seems to work… even stuff that supposedly should work in this situation.

So here I am looking for any more ideas???

EDIT:

My latest attempt has been to use SP_WHO to find which process is connected and then KILL it. But every time I KILL it, it comes back under another number immediately. Don't understand what in the world could possibly be causing such behavior.

EDIT2:

I just tried select * from master..sysprocesses where spid = <spid> to see the kpid which, if I understand correctly, correlates to the PID column in details tab on the task manager. Only problem is that the value is 0, which would mean it's the system idle process. Maybe I'm misunderstanding something here though.

Best Answer

Stopping the server and disabling the sa account seems like overkill to drop a database. Next time this happens, the following should be sufficient:

USE [master];
GO

ALTER DATABASE [dbname] SET OFFLINE
    WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE [dbname];

The connection that could be sneaking in could be from anything - SQL Server Agent, a linked server, IntelliSense in your query window, etc. Setting the database offline is much more direct approach than even moving into single user mode, never mind unnecessarily tracking down some rogue session in deprecated compatibility views...

Related Question