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

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: 2 days ago @ 7:44 PM
Points: 4,240, Visits: 4,284
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 @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #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: 2 days ago @ 7:44 PM
Points: 4,240, Visits: 4,284
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: 2 days ago @ 7:44 PM
Points: 4,240, Visits: 4,284
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:30 PM
Points: 12,918, Visits: 32,089
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

--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 #1449859
Posted Monday, May 6, 2013 2:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 418, Visits: 2,451
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
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 @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #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 @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #1449920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse