Single User Mode

Question

Post reply

Single User Mode

Welsh Corgi

SSC Guru

Points: 116520
More actions
May 4, 2013 at 12:09 pm

#293428

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/

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

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/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

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/

Lowell SSC Guru Points: 323504 More actions · Answer 4

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!

x SSC-Insane Points: 23660 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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