Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single User Mode Expand / Collapse
Author
Message
Posted Saturday, May 4, 2013 12:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 4,980, Visits: 4,777
If you set a Database in Single User Mode and you loose you conection to the session that you set single user mode what do you do to continue your work id you loose your connection? That is what if you accedendlty close you SPID?



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1449451
Posted Saturday, May 4, 2013 1:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 44,911, Visits: 42,944
Reconnect? Assuming that no one else has grabbed the sole allowed use, you can get back in.

If someone has, identify who, ask them politely to log off (or kill their session), then access the DB again.



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

Post #1449457
Posted Saturday, May 4, 2013 2:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 4,980, Visits: 4,777
Cool thank you Gail.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1449461
Posted Monday, May 6, 2013 1:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 4,980, Visits: 4,777
Thanks Gail.

If someone has the sole connection I would not be able to kill their session?

Worst case scenario would resrting the SQL Server Service start?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1449855
Posted Monday, May 6, 2013 1:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 14,437, Visits: 37,837
no need to stop the instance, i'd think.
you can force the database offline, and take it back with a couple of commands, i'd think:
ALTER DATABASE Test SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE Test SET ONLINE; USE Test;




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!
Post #1449859
Posted Monday, May 6, 2013 2:18 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 736, Visits: 4,497
Welsh Corgi (5/6/2013)
Thanks Gail.

If someone has the sole connection I would not be able to kill their session?

Worst case scenario would resrting the SQL Server Service start?


If you've set a particular DATABASE to single user mode, ie., , you should still be able to connect to the SERVER and have your connection set to another database, like for instance MASTER which is probably the default database associated with your login. The one exception I seem to notice that if in SSMS I set my DEFAULT database (as speced in SSMS properties for my "login") to the same database that I for whatever reason set to single user and THEN LOST MY CONNECTION, then yes I was pretty much locked out with SSMS. So you'd probably not want to set your database to SINGLE_USER if it is the DEFAULT DATABASE that your login is associated with.

So thats an interesting situation that I wasn't aware of.

Trying Lowells solution I was able to log back in with:

ALTER DATABASE TEST SET ONLINE

BUT ONLY if I added:

ALTER DATABASE TEST SET MULTI_USER

---> USING MY EXISTING CONNECTION THAT I HAD OPEN <---

If you've set your SERVER to single user mode, ie., startup parameter -m, that looks to me to be a different situation.

edit: grammar


to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1449876
Posted Monday, May 6, 2013 4:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 44,911, Visits: 42,944
Welsh Corgi (5/6/2013)
If someone has the sole connection I would not be able to kill their session?


You would be able to.
If someone has [grabbed the sole connection], identify who, ask them politely to log off (or kill their session), then access the DB again.



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

Post #1449918
Posted Monday, May 6, 2013 4:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 44,911, Visits: 42,944
patrickmcginnis59 10839 (5/6/2013)
The one exception I seem to notice that if in SSMS I set my DEFAULT database (as speced in SSMS properties for my "login") to the same database that I for whatever reason set to single user and THEN LOST MY CONNECTION, then yes I was pretty much locked out with SSMS. So you'd probably not want to set your database to SINGLE_USER if it is the DEFAULT DATABASE that your login is associated with.


Click the 'options' button on the login dialog, enter a different database name in the 'Connect to database' drop down, that overrides the default database. I suggest using 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

Post #1449920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse