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
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.