single user to multi user - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

single user to multi user

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-05 : 05:50:00

Dear all,
to restore one perticular backup set, i've made the database to single user mode, now it is not accepting me to connect to that again...
how can i bring the database to multiuser mode?

thanks in advance

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-05 : 05:54:50

[code]alter database db-name
set multi_user[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-05 : 05:59:46

i've tried in different ways even taken that to offline like that.....
actually there is some other problem...
any way i've latest backup with me....
now i'm trying to restore it with the same name, but it is saying there is lock on model database.
create database failed.

how can i remove the lock harsh............

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-05 : 06:02:54

dbcc opentran is not showing any locks

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-05 : 06:13:56

sorry for troubling you all....
now i'm disconnected and connected again to the instance....
now the problem solved..

thank you harsh

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 04:24:36

now the command was used by another user(set single_user)
now is there any way for me to bring back to multi user mode....
because the command
alter database test_fa set multi_user with rollback immediate
is not working on that instance

Vinod
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-06 : 04:45:23

Get the user who set it to single user mode to change it back as he will be the one with a login still.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 04:47:25

i too got the same thing..
but as a dba can we use some perticular command to kill his session?

Vinod
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-06 : 05:14:20

Not if you can't log into the instance at all, otherwise just kill the spid.

sp_who2 will give you the spid, then kill <spid>

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 05:49:30

Thank you RickD

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources