The database is in single-user mode, and a user is currently connected to it.

Posted on

Sometimes you might get the above error message from SQL Server when you try to change the user mode from MULTI to SINGLE.

You have to explicitly kill the connection in order to solve the problem. First you need the dbid of your database. Run the following command.

   1: SELECT name, database_id

   2: FROM sys.databases 

   3: WHERE name = 'db name'

Once you get the dbid you can check the connections to the database. Run the following command.

   1: SELECT spid FROM sysprocesses WHERE dbid = @dbid

spid is the connection process id. We have to kill all the spids  in order to run our command which sets the MULTI USER mode. For example think you get the spid as 10. Run the following command to kill the connection and set the database user mode.

   1: USE MASTER

   2: KILL 10

   3:  

   4: ALTER DATABASE [db name] SET MULTI_USER

   5: GO

Advertisements

One thought on “The database is in single-user mode, and a user is currently connected to it.

    […] Sometimes you might run into problems where you cannot set the database to MULTI USER mode; SQL Server complains with this error “The database is in single-user mode, and a user is currently connected to it.” […]

Comments are closed.