Single user mod

Question

Single user mod

Subhash-63067

SSCarpal Tunnel

Points: 4171
More actions
April 23, 2005 at 7:12 am

#88247

I have started sql server in single user mod using sqlservr.exe -c -m.
i have opened query analyzer and executed the command
dbcc checkdb ('test1', repair_fast),
it is giving me the error ' database need to be in single user mod'
i have already started the database in single user mod, then why it is telling me to start db in single user mod ?

subban

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 1

And at this moment if i try to open another QA, it will give an error meessage like db is in single user mod only one admin can connect at a time

subban

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 2

Yes, you are absolutely right! Your DB isn't in the single user mode

This command sqlservr.exe -c -m is used for restoring master DB and therefore you need to put SQL server in the single user mode. Not db but the server.

If you want to put other DB in the single user mode you must have to run alter database statement and put it into single user mode.

Hope this helps

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 3

Hai Vidas,

Ok, can u tell me how can i put, for example the DB 'TEST1' to single user mod ?

pls give me the commands pls

subban

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 4

Hi,

here you are:

alter database TEST1 set SINGLE_USER

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 5

Make sure that you will execute the following commands like checkdb in the same QA window or you'll be not allowed to do that (single_user)

Cheers

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 6

I've aslo found couple usefull options regarding to single_user mode:

ALTER DATABASE TEST1

set SINGLE_USER

WITH ROLLBACK IMMEDIATE

--and the second

ALTER DATABASE TEST1

set SINGLE_USER

WITH ROLLBACK AFTER 20 SECONDS

The first one terminates users connections immediate and rollback uncommited trans. The second one allows trans to be commited in the next 20sec then disconnect ussers.

Cheers

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 7

Hai,

Vida, thanks it works out....

with reference to this Single user Mod , i have one more query,

I was trying to restore a database, it was not allowing me to do that because users are connected to it. In this case if i do this

Alter database test1

set single_user

all the users will get exit and i will be able to do the resotre right?

pls suggest.

subban

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 8

Nope, if you want to restore your DB then you'll need to kill off the connections to it first, either nicely or otherwise (depends on the DB and who's using it).

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 9

hai Mike,

But this ALTER DATABASE TEST1 SET SINGLE_USER

will make all the connections teminated and will not allow further connection to the DB right ?

And this can be an easiest way to terminate all the connections right ?

pls correct me if am wrong

subban

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 10

Setting the DB to single-user mode wont kill off all of the other connections, you need to do that yourself.

If you don't kill their connections and there are other people using the system then it's possible that they'll take the single connection and prevent you from doing anything (until you kill their SPID).

Hope this explains it better

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 11

No. This statement ALTER DATABASE TEST1 SET SINGLE_USER you can execute when there are no coonected users. You must kill them manually.

Look at these the 2 statements which I wrote you previuosly

Cheers

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 12

Yes, ALATER DATBASE TEST1 SIGLE_USER , we can execute only when no users aer conencated....

My doubts are clear..... thanks a lot Vidas and Mike

subhash

subban

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 13

I'm glad to help you. I think that you didn't understand me what I wanted to say you about other two statement...

If you will execute

ALTER DATABASE test1

set SINGLE_USER

WITH ROLLBACK IMMEDIATE

it will automatically kill other users and put your db into single user mode. You wouldn't need to kill them manually. All work for you will be done by SQL Server

Cheers

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 14

Ok, now i am clear. so i hope this i can apply to my restore issues also

subhash

subban