1

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.

  • 1
    (1) you shouldn't be using sysprocesses anymore - this is a backward compatibility view that will disappear at some point. (2) USE master; ALTER DATABASE ... SET OFFLINE WITH ROLLBACK IMMEDIATE; should have been enough to kick that one user out. – Aaron Bertrand Apr 3 '14 at 0:14
1

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...

| improve this answer | |
  • Wouldn't let me. I totally tried! I literally couldn't do anything without it telling me there was already a connection open to it and I couldn't kill the connection (well, if I killed it it came back immediately). But I agree this 'should' be the correct answer... never seen anything like what I experienced today before. – BVernon Apr 3 '14 at 3:30
  • FYI, just to be clear: I wasn't trying to put it into single user mode... it had already gotten into single user mode (I don't know how) and I couldn't get it out. So that's why this solution wouldn't work for me. As for how the connection kept coming back is still a mystery... no linked server and no query window open that it would be coming from (and everything is on my local machine... no remote connections). I'm going to look to see if there's anything that Sql Agent might have been doing but that's also highly unlikely for this database. Was just really, really strange. – BVernon Apr 3 '14 at 3:36
  • @BVernon I suspect it was your own. In any case, if you were trying to get it out of single user (I thought you were trying to drop it?), you alter it to MULTI_USER. You should also be able to use other workarounds, such as restore with replace, or just renaming the database first... – Aaron Bertrand Apr 3 '14 at 12:11
  • I was trying to drop it... but as part of my many efforts to do so I also tried to get it out of single user mode although I ran into the same issue there: there was already another connection open to it so it wouldn't let me. I didn't try a restore with replace, but if it wouldn't let me delete it while there was another connection open (even with the checkbox to drop connections checked) then I doubt it would have let me do that either. Couldn't rename either for same reason I could do the other things. – BVernon Apr 3 '14 at 21:18
  • And I'm not positive I know what you mean when you say you suspect it was my own... although I suspect you're implying I had a query window open that was making the connection. I did not. – BVernon Apr 3 '14 at 21:21
0

YES!!! I DID IT!!!

When I ran select * from master..sysprocesses where spid = <spid>, I realized that the connection was being made from the sa user account. I still don't understand what in the world was doing it, but I temporarily disabled the sa account and restarted the server. It wasn't able to make a connection again and I dropped the database.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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