Sql-server – Single User mode can’t be turned off and no users are connected

sql serversql-server-2008sql-server-2008-r2

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?

Best Answer

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

Related Question