I am recreating a scenario when somebody or a process set database to single_user and I need in my script to set it to multi_user.
So far I tried to:
alter database test1
set multi_user
with rollback immediate
as well as with no_wait, but nothing helped, I get an error:
"Changes to the state or options of database 'test1' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."
The spid where I've set it to multi_user has "use master"; so sys.sysprocesses and sys.dm_exec_requests don't show me anything.
Is there a way to deal with it?
Thanks
May 17, 2021 at 5:46 pm
I think this post will tell you how to do it:
https://stackoverflow.com/questions/49741600/database-stuck-in-a-single-user-mode-in-sql-server
To paraphrase, run
SELECT spid
FROM master..sysprocesses
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
and kill that SPID.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 17, 2021 at 6:32 pm
No, you can miss user(s) of the db that way. Instead, force the db offline; then bring it back online and immediately USE the db yourself and then set it into MULTI_USER mode.
USE master;
ALTER DATABASE test1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE test1 SET ONLINE;
GO
USE test1;
ALTER DATABASE test1 SET MULTI_USER;
GO
USE master;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2021 at 1:20 pm
Thanks both Brian and Scott, but unfortunately nothing worked in my case.
@Brian: This query returns empty result set:
SELECT spid
FROM master..sysprocesses
WHERE spid > 50 AND dbid = DB_ID('test1')
After the 1st statement
use master
go
ALTER DATABASE test1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
go
I receive an error:
Msg 5064, Level 16, State 1, Line 15
Changes to the state or options of database 'test1' 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 15
ALTER DATABASE statement failed.
May 18, 2021 at 4:07 pm
D'OH, sorry, didn't realize SQL wouldn't let you OFFLINE the db in that case. But, one of the reasons I avoid SINGLE_USER like the plague is the issues it causes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2021 at 4:25 pm
Two thoughts. First, does:
EXEC SP_who2
give you any results for a user connected to the database test1? If there are a lot of connections, it can be a pain to scroll through that, but it MAY tell you which SPID is using that database...
Second (probably dumb) thought - do you have a connection to the database open in Object explorer portion of SSMS? If so, disconnect from there as it MAY be SSMS using up the single user connection.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 18, 2021 at 4:41 pm
You could use Activity Monitor to list all of the processes - but you could also use this code, which is basically the same code as used by Activity Monitor for 2016 and greater systems.
With profiled_sessions
As (
Select Distinct
session_id profiled_session_id
From sys.dm_exec_query_profiles
)
Select [Session ID] = s.session_id
, [Login] = s.login_name
, [Database] = iif(p.dbid = 0, N'', isnull(db_name(p.dbid), N''))
, [Task State] = isnull(t.task_state, N'')
, [Command] = isnull(r.command, N'')
, [Application] = isnull(s.program_name, N'')
, [Wait Time (ms)] = isnull(w.wait_duration_ms, 0)
, [Wait Type] = isnull(w.wait_type, N'')
, [Wait Resource] = isnull(w.resource_description, N'')
, [Blocked By] = isnull(convert(varchar, w.blocking_session_id), '')
, [Head Blocker] = iif(r2.session_id Is Not Null And (r.blocking_session_id = 0 Or r.session_id Is Null), N'1', N'')
, [Total CPU (ms)] = s.cpu_time
, [Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024
, [Memory Use (KB)] = s.memory_usage * (8192 / 1024)
, [Open Transactions] = isnull(r.open_transaction_count, 0)
, [Login Time] = s.login_time
, [Last Request Start Time] = s.last_request_start_time
, [Host Name] = isnull(s.host_name, N'')
, [Net Address] = isnull(c.client_net_address, N'')
, [Execution Context ID] = isnull(t.exec_context_id, 0)
, [Request ID] = isnull(r.request_id, 0)
, [Workload Group] = isnull(g.name, N'')
, ps.profiled_session_id
From sys.dm_exec_sessions s
Left Join sys.dm_exec_connections c On s.session_id = c.session_id
Left Join sys.dm_exec_requests r On s.session_id = r.session_id
Left Join sys.dm_os_tasks t On r.session_id = t.session_id
And r.request_id = t.request_id
--==== In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
--==== waiting for several different threads. This will cause that thread to show up in multiple rows
--==== in our grid, which we don't want. Use OUTER APPLY and TOP 1 to select the longest wait for each thread,
--==== and use it as representative of the other wait relationships this thread is involved in.
Outer Apply (Select Top 1
*
From sys.dm_os_waiting_tasks wt
Where wt.waiting_task_address = t.task_address
Order By
wt.wait_duration_ms desc
) w
Left Join sys.dm_exec_requests r2 On s.session_id = r2.blocking_session_id
Left Join sys.dm_resource_governor_workload_groups g On g.group_id = s.group_id
Left Join sys.sysprocesses p On s.session_id = p.spid
Left Join profiled_sessions ps On ps.profiled_session_id = s.session_id
Where s.is_user_process = 1
--And r.command Is Not Null -- only show processes with active commands
And s.session_id <> @@spid -- don't need to show this session
Order By
s.login_name --[Host Name] --r.command desc
, s.session_id;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Hi, you got correct query in first response from https://stackoverflow.com
SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('YourDatabaseName')the session that is using DB will be holding at least S lock on database level.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply