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”
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!
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!
Your post is the only one that helped me get my database back online from single-user. Keep sharing the knowledge.
Aho,
boyd
that’s nice to hear – and I’m not even an SQL Server DBA ;)
it works perfectly. Thanks
Worked for me in one query…
Thanks!
Ahhh saved my bacon.
Cheer sdude.
Nicely Done! The commands/scripts work without flaw!
It worked for me, this saved my time, thanks :)
Worked just fine. Thanks.
Gud one !!!
This works excellently thanks…..
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!
Worked perfectly.. saved my time.
God bless you…..
100% perfect save my time and life god bless you
you saved my day with this article..
Thanks for sharing the wisdom
Thanks a lot for this simple but valuable information !!!
Great!!!!!!!!!! it helped me a lot…..
Thank you! Saved my ‘you know what’.
Exactly what I needed. Thank you!
Worked perfectly……..Thanks
perfect!
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
thanks it’s really help me.
beautiful post
This was great. I used the Activity monitor to see who was blocking but this post definitely helped.
Very very nice…Thanks.
Did what it was supposed to do!
Found and used this for the second time. Thanks!!!!!!!!
Thanks, It helped me in 2 ways.
This one is the only one that helped me after trying for long time.
Thank you!
Excellent post – straight forward worked great. Even though it’s old info – it worked on SQL 2008.