Bringing Database back from Single User to Multi User mode with active connections

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

Leave a Reply

Your email address will not be published. Required fields are marked *