Remove SQL Server database from single-user mode

Braindump of a session where I had to remove a SQL Server database from single-user mode.

execute sp_dboption
1> exec sp_dboption 'testdb01', 'single user', 'FALSE';
2> go
Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1
Changes to the state or options of database 'testdb01' 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, Server REMIDIAN01, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Now I can use TSQL below to find all the database sesions connected to this database.

TSQL script to retrieve client sessions per 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 = 'testdb01'
go

Kill the particular session(s) with the ;kill’ command.

1> kill 51
2> go
1>

Now I can ‘remove’ the database from Sinlge user mode.

1> exec sp_dboption 'testdb01', 'single user', 'FALSE'
2> go
1>

91 thoughts on “Remove SQL Server database from single-user mode

  1. Great article. However, I was being blocked by a squirrely account that would re-connect so quickly, I couldn’t get the DB out of single mode before it reconnected.
    My solution was to look at the field [loginname] from the first query (second paragraph). I locked-out that account and was able to finish the job.
    I thought I’d pass-along that extra tip.
    Thanks for the help!

  2. I have found that even after killing the process, I couldn’t alter the database back to multi-user mode as desired and I used this simple command before hand and worked like a charm!

    Here you go!

    SET DEADLOCK_PRIORITY HIGH

  3. I just enter to say thank you very much.

    I didn’t need to kill any process, I just stoped my sql server to make sure I was the first one to log in, after that I ran your suggested command, and worked perfect!

    Thank you very, very, very much from Guatemala!

    PD. I am using SQL Server 2000

  4. When I run the TSQL script to retrieve client sessions per database, I get an error message that the database is already open and can only have one user at a time, so I can’t even find the process that needs to be killed.

  5. This method does not work on Server 2012.

    You have to use this statement instead:

    ALTER DATABASE [testdb01] SET MULTI_USER WITH NO_WAIT

    If there is a connection you can either restart the SQL Server service as mentioned above, or you can go into SQLCmd (C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
    ) and do the first part of the step up to the execute sp_dboption and at that point run the Alter Database statement.

  6. Found the solution which I had forgotten just in the nick of time as I was dealing with a production implementation. Great Job!!!! Thanks!

  7. I found that I had non-user sessions (spid < 50) and they were blocking me. Restarting the service cleared those and I was able to use this process.

  8. its better you run the Query at one time after you check which user it catching the DB

    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 = ‘db_name’
    go

    then you run another query with this code
    kill — the connection to the database in single user mode
    exec sp_dboption ‘sms_ntc’, ‘single user’, ‘FALSE’
    go
    alter database sms_ntc set multi_user with rollback immediate

  9. Just sp_who2 find the spid which is using Database genreally it will be single session using this db because you are set to single user mode kill that process and set it multi user and use it

  10. Thanks for the tip. It worked like a charm. I was able to get the database off of single user mode and restore it from backup.

Leave a Reply

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