SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database stuck in single user mode


Database stuck in single user mode

Author
Message
jimmie.roberts
jimmie.roberts
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 4
I use SQL Studio and then Object Explorer to navigate thru SQL. Right after I excuted this command:

ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I could see in Object Explorer that the TEMP DB was in Single-User mode. But, when I right clicked the db to perform a task, (a "force" restore), I got an error saying that I couldn't access the TEMP DB. I closed SQL Studio and then restarted it and now when it tries to connect to the Instance, I get an error that says login failed for user 'sqladmin' Cannot open default database. I am configured to connect via Windows Authentication but my username is greyed-out (I cannot modify it) and the password field is blank. I try connecting via SQL authentication and that fails also.

That ALTER DATABASE command really messed me up.

Do you know how I can modify who I am logging in as?

Thanks,
Jimmie
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228579 Visits: 46342
In the management studio login dialog, click on Options (bottom right). That will take you to another dialog where you can change the default database. Pick master.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


jimmie.roberts
jimmie.roberts
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 4
Thank you! That was it. Duh.

Now, I can go into the properties of my DBs and then I click on Options and I can set the Restrict Access setting to Multi-User!

That's it! Thank you again.

Jimmie
Abhijit More
Abhijit More
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1991 Visits: 767
You can identity it by running the sysprocesses query.
When you run the query you will receive the same message saying "database is in single_user mode".
If you check the resultset you will get some spid see the last spid add one in that you will get the spid accessing the single_user database.

Kill the spid

I hope this works.

Abhijit - http://abhijitmore.wordpress.com
Divya Agrawal
Divya Agrawal
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 604
you can execute sp_who2 to see the processes

--Divya
Rome1981
Rome1981
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 106
You can get the SPID from activity monitor too.
Abhijit More
Abhijit More
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1991 Visits: 767
I was also facing the same issues even I tried all the solutions mentioned here & some other solution except stopping or pausing the server.

I used another trick for it.
1. Query the sysprocessess
2. You will receive the same message stating "Changes to the state or options of database 'test' cannot be made at this time. bla..bla..bla"
3. Check the resultset, get the highest spid from the result. add "1" value to it. say if you got spid as 75 you have to kill the spid 76.
4. Your whole & sole connection to database is killed now.
5. use "Alter database [MyDatabase] set multi_user with rollback immediate"

Abhijit - http://abhijitmore.wordpress.com
sramakrishnan
sramakrishnan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
Excellent, worked like a charm. Thanks.
- Subah Ramakrishnan
rajput_amol159
rajput_amol159
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 190
Hi,

use the same session for which you have used to make the database single_user. By default that session will have the access. run the

Alter database db_name with multi_user command in same window. Yuo will get the access and database will be in multi user mode.

Thanks
Amol Solunkhe
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73373 Visits: 40968
note you replied to a thread more than two years old.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search