Bringing Database back from Single User mode to Multi User mode for Database with active connection:
Some times you will need to bring a database back from single user mode.
If we issue the below command,
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET multi_USER
GO
It errors out:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'AdventureWorks' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
For conditions like this:
Use the below query to find out the active connection to the database
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 = 'AdventureWorks'
go
or
sp_who2
Then
Kill spid
Go
But sometimes you can not see any active connections with above commands when the connection issuing the single user mode is still connected and its connection is to master database linked to the AdventureWorks database.
For this kind of situation use Activity monitor or the query below to find the query which is connected to master but issuing a command to set database into single user mode.
select top 100
c.session_id,
t.text,
s.login_time,
s.host_name,
s.program_name,
s.host_process_id,
s.login_name,
s.status,
s.cpu_time,
s.memory_usage,
s.total_scheduled_time,
s.total_elapsed_time,
s.last_request_start_time,
s.last_request_end_time,
s.reads,
s.writes,
s.logical_reads,
s.is_user_process,
c.most_recent_session_id,
c.connect_time,
c.auth_scheme,
c.num_reads,
c.num_writes,
c.last_read,
c.last_write,
c.net_packet_size,
c.client_net_address,
c.client_tcp_port,
c.local_net_address,
c.local_tcp_port
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s on c.session_id = s.session_id
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) t
order by s.cpu_time desc
Then you will find a query with relevant information to kill the query like below:
Session_id Text
62 ALTER DATABASE [AdventureWorks] SET single_USER -- WITH ROLLBACK IMMEDIATE
After killing the session you can set the database back to multi user mode by making a connection to the database like
use master
go
ALTER DATABASE [AdventureWorks] SET multi_USER
GO