Remove SQL Server database from single-user mode

  • Published on Jan 23 2008
  • # 20,593
  • mssql

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>

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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

  1. Flea-Flicker says:
    August 29, 2012 at 3:40 am

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

  2. Bala Inampudi says:
    August 29, 2012 at 4:51 pm

    Thanks, It worked for us.

  3. Virendra Mackwana says:
    September 20, 2012 at 5:14 pm

    Brilliant!!! I found this after spending an hour on same issue and it worked so smooth…

  4. Chathura says:
    October 31, 2012 at 8:21 am

    Thanks..this worked

  5. Jezza :P says:
    October 31, 2012 at 1:37 pm

    THANKS!!!! :D Solved an urgent issue.

  6. Daniel says:
    November 17, 2012 at 11:00 pm

    Thank you. This really helped me.

  7. mahiar aeini says:
    December 11, 2012 at 5:15 pm

    Thank you so much . . .

  8. Akram says:
    December 12, 2012 at 9:40 pm

    Brilliant!!! Worked like a charm!!!

  9. Mike says:
    January 8, 2013 at 10:03 pm

    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.

  10. André says:
    January 22, 2013 at 2:50 pm

    Thank you very very much!
    Works fine!

  11. Raman says:
    April 5, 2013 at 8:50 am

    thanks it worked for me