Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I have a database that is in Single User Mode. When I run this:

exec sp_dboption 'MyDb', 'single user', 'FALSE'

It fails, telling me that there is still a user connected.

But when I run both of these:

exec sp_who2


select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'MyDb'

I get no connections for MyDb.

How can this be, and how can I get my database out of single user mode?

share|improve this question
you aren't the user are you..? Do USE master before ALTER DATABASE (the new way to do this) – gbn Feb 2 '12 at 21:28
No, me and my DBA made very sure that was not the case. I closed everything down and we double checked all connections from my machine. (Again, no connections show up.) – Vaccano Feb 2 '12 at 21:29
remidian.com/2008/01/… This blogpost helped me fix my issue. – Moulde Apr 29 at 8:15

2 Answers

up vote 2 down vote accepted

If a user - or process- is using multiple databases, sp_who2 can only report on one of the databases per spid.

Have you tried something like the following - it will rollback all transaction not completed:

ALTER DATABASE [mydb] set multi_user with rollback immediate

share|improve this answer
Thanks for the idea, but that did not work either... – Vaccano Feb 2 '12 at 19:48
What was the error message? What do the SQL Server logs say? Does it say when the database went into single user mode? – Peter Schofield Feb 2 '12 at 20:48
Same error as I always get (The database is in single-user mode, and a user is currently connected to it.) I am afraid I don't know where to look for the logs. (I am not a real DBA, just a developer admining a development database.) – Vaccano Feb 2 '12 at 21:10
We were able to get it out of single user mode (finally) but it kept going right back in. Finally I had the drop the database and re-create it from scratch. – Vaccano Feb 2 '12 at 21:30
Why did OP accept this answer if it doesn't work? – Moulde Apr 29 at 8:07
up vote 1 down vote

You won't see other folks connection unless you are sysadmin or have VIEW SERVER STATE permissions because of MetaData visibility

Anyway, force it like this with ALTER DATABASE
Note: sp_dboption is deprecated, back to SQL Server 2000

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
share|improve this answer

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.