Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Database stuck in single user mode Expand / Collapse
Author
Message
Posted Thursday, June 25, 2009 3:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:10 PM
Points: 4, 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
Post #742242
Posted Thursday, June 25, 2009 3:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 39,883, Visits: 36,230
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 2008, MVP
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

Post #742249
Posted Thursday, June 25, 2009 4:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:10 PM
Points: 4, 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
Post #742267
Posted Monday, December 21, 2009 4:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
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
Post #837075
Posted Tuesday, December 22, 2009 10:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
you can execute sp_who2 to see the processes

--Divya
Post #838075
Posted Tuesday, December 22, 2009 1:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 2:20 PM
Points: 61, Visits: 106
You can get the SPID from activity monitor too.
Post #838236
Posted Wednesday, December 23, 2009 2:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
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
Post #838412
Posted Tuesday, July 12, 2011 2:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 2:07 PM
Points: 1, Visits: 11
Excellent, worked like a charm. Thanks.
- Subah Ramakrishnan
Post #1140645
Posted Monday, May 6, 2013 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:55 AM
Points: 2, Visits: 169
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
Post #1449774
Posted Monday, May 6, 2013 10:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 12,897, Visits: 32,103
note you replied to a thread more than two years old.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449783
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse