Help to change from Single User Mode

Question

Post reply

Help to change from Single User Mode

Andrew J

Mr or Mrs. 500

Points: 507
More actions
February 17, 2006 at 3:21 pm

#90608

Hi all,
somehow, I got one of my database is set as single user mode... on E. manager, it says like :
database_name(Single USer)
and I can not do anything with this database.. cant open the table, backup, add user, etc.
seems like there is some process that locking this database..
Please advise how to make things normal again
i mean, to remove the single user mode.

Thanks Guys,

Best regards

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Using QA:

USE Master

sp_dboption '<your DB name>', 'SINGLE_USER', false

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 2

It does not work

I got this message:

"Server: Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database 'mydb' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Server: Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

sp_dboption command failed."

How to solve it?

seems like we need to clear the user connection, please advise on this issue.

thanks

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Use EM to find out who is connected to that database. I imagine it is your EM session. Shut down your EM and try the QA command again.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 4

EM was closed when I run that query..

however, when I check it using EM, the datbase is locked, by its user database.. the status is sleeping... awaiting command..

and when I try to kill this process, it won't removed.. it still there

please advise

John Rowan SSC Guru Points: 56440 More actions · Answer 5

When you say the database is locked by its user database, what does that mean? Doesn't it give you a user name? Do you recognize the host name?

Right-click on the database and select 'Detach'. When the Detach database window comes up, clear the database connection. Then click cancel. You do not want to detach it, just clear the connection. Then open QA and run the command I gave you.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 6

Andrew J

Mr or Mrs. 500

Points: 507

February 17, 2006 at 4:34 pm

#621840

Thanks so Much

it works

Pawan Kumar-1970 SSC Rookie Points: 36 More actions · Answer 7

Check your terminal connection too.

It may be that you are having two session on it.One running and one disconnected by earlier session.

Logged of the disconnected session and run

sp_dboption '<your DB name>', 'SINGLE_USER', false

It works perfectly.

Cheers......!

Pawan kumar

pawan_kv@hotmail.com

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 8

This was a big help... especially going into detach mode to drop the attached user. Thanks.

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman