Remove SQL Server database from single-user mode

  • Published on Jan 23 2008
  • # 46,429
  • 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>

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

  1. Boyd says:

    Your post is the only one that helped me get my database back online from single-user. Keep sharing the knowledge.

    Aho,

    boyd

    1. remivisser says:

      that’s nice to hear – and I’m not even an SQL Server DBA ;)

  2. Basu says:

    it works perfectly. Thanks

  3. Serge says:

    Worked for me in one query…

    Thanks!

  4. Benjamin Cramphorn says:

    Ahhh saved my bacon.

    Cheer sdude.

  5. Nicely Done! The commands/scripts work without flaw!

  6. narendra says:

    It worked for me, this saved my time, thanks :)

  7. Test User says:

    Worked just fine. Thanks.

  8. shakil says:

    Gud one !!!

  9. Sanjeev says:

    This works excellently thanks…..

  10. Tgolisch says:

    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!

  11. PankajRajput says:

    Worked perfectly.. saved my time.
    God bless you…..

  12. Dharmesh says:

    100% perfect save my time and life god bless you

  13. pkt says:

    you saved my day with this article..
    Thanks for sharing the wisdom

  14. Shaik says:

    Thanks a lot for this simple but valuable information !!!

  15. sai says:

    Great!!!!!!!!!! it helped me a lot…..

  16. scott says:

    Thank you! Saved my ‘you know what’.

  17. Evan says:

    Exactly what I needed. Thank you!

  18. BT says:

    Worked perfectly……..Thanks

  19. manideep says:

    perfect!

  20. Chris K says:

    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

  21. Nikunj says:

    thanks it’s really help me.

  22. aisha says:

    beautiful post

  23. Doug Rixmann says:

    This was great. I used the Activity monitor to see who was blocking but this post definitely helped.

  24. shan says:

    Very very nice…Thanks.

  25. Naveen says:

    Did what it was supposed to do!

  26. EricDallas says:

    Found and used this for the second time. Thanks!!!!!!!!

  27. Boss says:

    Thanks, It helped me in 2 ways.

  28. Stewie says:

    This one is the only one that helped me after trying for long time.

    Thank you!

  29. DamienB says:

    Excellent post – straight forward worked great. Even though it’s old info – it worked on SQL 2008.

  30. Gopinath says:

    Thanks….
    Very good post.

  31. Sp says:

    This worked to perfection. Thanks!!

  32. GUALHERO says:

    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

  33. Sachin says:

    You the man.

  34. Just like Boyd said, “Your post is the only one that helped me get my database back online from single-user”.
    Thanks.

  35. Dan says:

    Cheers dude, fixed my little pain in the arse of a problem

  36. jimmylimmy says:

    Only thing that worked! thanks!

  37. Max says:

    Awesome job – thank you!

  38. Nikolay says:

    Thank you!
    Save a lot of time!

  39. Frans says:

    Thank you very much I followed your script and it all worked.

  40. Sharon says:

    Finally a solution!

  41. David says:

    Great solution.

  42. Very nice.. thanks for sharing. Worked!

  43. yadav says:

    Nice solution, you saved my time.

  44. Nancy says:

    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.

  45. Aaron says:

    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.

  46. Ashif says:

    Thanks 100% working.

  47. Ricardo Julia says:

    Saved my bacon! Congratulations from Puerto Rico

  48. Thanks for sharing! Works great and saved us a bunch of time.

  49. Flea-Flicker says:

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

  50. Bala Inampudi says:

    Thanks, It worked for us.

  51. Virendra Mackwana says:

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

  52. Chathura says:

    Thanks..this worked

  53. Jezza :P says:

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

  54. Daniel says:

    Thank you. This really helped me.

  55. mahiar aeini says:

    Thank you so much . . .

  56. Akram says:

    Brilliant!!! Worked like a charm!!!

  57. Mike says:

    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.

  58. André says:

    Thank you very very much!
    Works fine!

  59. Raman says:

    thanks it worked for me

  60. Paul says:

    Worked like a charm, but make sure you execute the last two statemants as one.

  61. layth says:

    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

  62. Ravi says:

    Thanx ……..its worked

  63. gautam kathrotiya says:

    thanks for awesome idea………..

  64. Suresh Shukla says:

    Awesome, It worked for me. Thanks.

  65. Gilberto Melendez says:

    I’m believer. The process works !

  66. Halli says:

    So much appreciated, thanks for saving my day!

  67. Blasia says:

    Thank you so much ||

  68. Hithem says:

    Thanks a lot, Great Solution

  69. Swaradhvi says:

    Thanks for this remedy. it helped me to bring back my database to multi_user mode.

  70. kwena says:

    It worked for me too, thanks a mill

  71. Chris says:

    Thanks! You saved my day!!!!!

  72. naveen says:

    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

  73. CCC Adam says:

    thanks man for the tip! it saved us a lot of time to investigate : )

  74. SP Admin says:

    Thanks this is the only post that I found that actually worked!!!

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>