How to find a user and a spid that holds database in single_user mode?

  • 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

     

  • 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!

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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')


    @scott:

    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.

     

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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!

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply