How to recover from a single user mode database

If the SQL Server database you are working with are set to single user mode, it accepts only one active connection. You might not be able to access the database at all.


1. First you must ensure that there are no other active connections to the database. You could find out by running this SQL:

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 = 'dbname'

go

2. Then use the spid and kill that connection with

kill <spid>

3. Lastly, you could set the database to multiuser with this command:

ALTER DATABASE <database name>
SET MULTI_USER;