Can't find what you're looking for? Use of one of the search websites below …

HomemssqlRemove SQL Server database from single-user mode

Remove SQL Server database from single-user mode

Published on Wednesday, January 23, 2008

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>

30 Responses to “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

  2. remivisser says:

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

  3. Basu says:

    it works perfectly. Thanks

  4. Serge says:

    Worked for me in one query…

    Thanks!

  5. Benjamin Cramphorn says:

    Ahhh saved my bacon.

    Cheer sdude.

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

  7. narendra says:

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

  8. Test User says:

    Worked just fine. Thanks.

  9. shakil says:

    Gud one !!!

  10. Sanjeev says:

    This works excellently thanks…..

  11. 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!

  12. PankajRajput says:

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

  13. Dharmesh says:

    100% perfect save my time and life god bless you

  14. pkt says:

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

  15. Shaik says:

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

  16. sai says:

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

  17. scott says:

    Thank you! Saved my ‘you know what’.

  18. Evan says:

    Exactly what I needed. Thank you!

  19. BT says:

    Worked perfectly……..Thanks

  20. manideep says:

    perfect!

  21. 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

  22. Nikunj says:

    thanks it’s really help me.

  23. aisha says:

    beautiful post

  24. Doug Rixmann says:

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

  25. shan says:

    Very very nice…Thanks.

  26. Naveen says:

    Did what it was supposed to do!

  27. EricDallas says:

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

  28. Boss says:

    Thanks, It helped me in 2 ways.

  29. Stewie says:

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

    Thank you!

  30. DamienB says:

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

Leave a Reply