Database in single-user mode; how to change it programmatically ?

  • Recently we had multiple production failures because one database was in single-user mode. I am trying to recreate this scenario to write a script that will kick out that connection that was holding database in single-user.

    In SSMS window 1 I run the following code:

    use master

    go

    alter database test

    set single_user

    with rollback immediate

    In window 2 I run this:

    select user_access_desc

    from sys.databases

    where name = 'Test'

    Indeed, it shows SINGLE_USER.

    Now I want to find out who is connected in order to kill that spid:

    select d.name, p.*

    from sys.databases d join sys.sysprocesses p

    on d.database_id = p.dbid

    where d.name = 'Test'

    It shows nothing.

    I slightly change this query for that specific SPID that's holding database in window 1:

    select d.name, p.*

    from sys.databases d join sys.sysprocesses p

    on d.database_id = p.dbid

    where spid = 67 --d.name = 'Test'

    Yes, it shows one record, but the database name is master, not test. And indeed, I run it from the context of master. Also tried sp_who, sp_who2, sys.dm_exec_requests - nothing works. For dm_exec_requests it shows nothing because it does not run at the current moment.

    Any other solutions to catch this SPID with a script?

    Thanks

  • There's no need to kick out the connection manually before you can revert to MULTI_USER. You can use ROLLBACK IMMEDIATE:

    ALTER DATABASE Whatever SET MULTI_USER WITH ROLLBACK IMMEDIATE

    -- Gianluca Sartori

  • I tried it, but got the following error:

    Msg 5064, Level 16, State 1, Line 1

    Changes to the state or options of database 'Test' 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.

  • I don't have a SQL instance to test right now, but here's a quick thought: try to query sys.dm_tran_locks and see which session is holding a lock on that database.

    -- Gianluca Sartori

  • spaghettidba (3/10/2015)


    I don't have a SQL instance to test right now, but here's a quick thought: try to query sys.dm_tran_locks and see which session is holding a lock on that database.

    Thanks Gianluca for a good tip.

    Here is the query that I developed:

    select DB_NAME(resource_database_id),

    *

    from sys.dm_tran_locks

    where resource_type = 'database'

    However, it shows not only my Test databases, but also several others. The only difference that I found between my Test connection and the others is that for column request_owner_type I have 'SESSION' value while the rest connections show 'SHARED_TRANSACTION_WORKSPACE'.

    I could not find much info about this, but nevertheless updated my query to this:

    select request_session_id

    --DB_NAME(resource_database_id),

    --*

    from sys.dm_tran_locks

    where resource_type = 'database'

    and request_owner_type = 'SESSION'

    It shows correct SPID number. Now I can easily kill it and alter the database.

    But what surprised me is that it shows 'S' for request_mode, I expected to be exclusive.

  • It does take an exclusive lock, but only while it is actually altering the database.

    Once that's completed, it holds the S lock.

    Cheers!

Viewing 6 posts - 1 through 6 (of 6 total)

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