July 13, 2010 at 4:02 pm
From cmd prompt I type sqlservr.exe -m and the databases are all started up.
Next I click Start->SSMS->Connect and I get this error:
"Login failed for user '<instance>\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time."
SQL Server is in Stopped mode. I believe I should be able to start up SSMS using the GUI at this point. What am I doing wrong?
Thank you.
July 13, 2010 at 4:15 pm
hxkresl (7/13/2010)
From cmd prompt I type sqlservr.exe -m and the databases are all started up.Next I click Start->SSMS->Connect and I get this error:
"Login failed for user '<instance>\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time."
This is exactly what you are asking SQL Server to do, sqlservr.exe -m tells SQL Server to start in "single user" mode.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2010 at 4:22 pm
So at this point I am limited to using only the cmd line until I can get the underlying problem solved?
July 13, 2010 at 4:26 pm
hxkresl (7/13/2010)
So at this point I am limited to using only the cmd line until I can get the underlying problem solved?
Not clear to me what the problem is.
I assume that starting SQL Server from the command line means something really bad has happened and serious troubleshooting/fixing is going on.
If this is the case you really don't want other people login into the system.
If this is not the case I'm not sure why SQL Server is being started that way.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2010 at 4:35 pm
Paul, I should have been clear. I am tinkering. I was simulating a problem with the master database being off-line. I stopped SQL Server and tried starting up SSMS in single user mode.
I wanted to get into SSMS but found I couldn't, but this went against expectation because everywhere it says I can start SSMS at this point.
I am just trying to understand whether I am able to get into the GUI after successfully executing sqlservr.exe -m.
Thanks and I'll take a look at the book.
July 13, 2010 at 4:45 pm
First, stop saying you are starting SSMS in single user mode. You start SSMS, the mode in which SQL Server is started is irrelevant.
The error you are getting is that SSMS tries to make more than one connection. Therefore if you are using the GUI, you will get errors. If you don't connect the object browser, you ought to be able to only connect the query editor. That should be a single connection.
July 13, 2010 at 5:07 pm
Steve Jones - Editor (7/13/2010)
First, stop saying you are starting SSMS in single user mode. You start SSMS, the mode in which SQL Server is started is irrelevant.The error you are getting is that SSMS tries to make more than one connection. Therefore if you are using the GUI, you will get errors. If you don't connect the object browser, you ought to be able to only connect the query editor. That should be a single connection.
OK. I stand corrected. I should say starting SQL Server in single user mode, not SSMS. Understood.
Bear with me. How to connect to query editor in single user mode?:ermm:
July 13, 2010 at 5:19 pm
Couple things. Make sure SQL Agent doesn't start. That will connect and use your one connection.
You will also not want to use the Object Explorer since that takes a connection. You'll only want the query window.
most people just start SQL Server in a command prompt with the -m parameter. Then in another command prompt, connect with SQLCMD. This will give you a cmmand line interface to run queries.
July 13, 2010 at 5:36 pm
If the idea is to start SQL Server in "normal" mode you can either...
1- Start the SQL Server Service or,
2- Command line: sqlservr.exe (assuming this is the default instance)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2010 at 5:44 pm
Steve Jones - Editor (7/13/2010)
Couple things. Make sure SQL Agent doesn't start. That will connect and use your one connection.You will also not want to use the Object Explorer since that takes a connection. You'll only want the query window.
most people just start SQL Server in a command prompt with the -m parameter. Then in another command prompt, connect with SQLCMD. This will give you a cmmand line interface to run queries.
this cinched it up for me. I had SQL Agent running and was not aware of what all consumed the connection that I needed. Thanks!
July 17, 2010 at 11:43 pm
another question about starting SQL Server in single user mode. If prior to shutting down SSMS I had enabled xp_cmdshell using sp_configure, then from the command shell in single user mode, could I potentially issue T-SQL commands that could back up my database/s?
July 18, 2010 at 6:30 pm
xp_cmdshell and T-SQL backup commands have nothing to do with one another.
T-SQL commands are run inside SQL Server, and you should be able to run them in single user mode.
Xp_cmdshell is for running DOS/Command prompt commands.
July 18, 2010 at 7:26 pm
Steve Jones - Editor (7/18/2010)
xp_cmdshell and T-SQL backup commands have nothing to do with one another.T-SQL commands are run inside SQL Server, and you should be able to run them in single user mode.
Xp_cmdshell is for running DOS/Command prompt commands.
Thanks Steve. That was my original understanding. I got that idea from this article by Gregory A Larson:
http://www.databasejournal.com/features/mssql/article.php/3372131/Using-xpcmdshell.htm
excerpt:
"xp_cmdshell" is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code. If needed the output of these commands will be returned to the calling routine."
July 18, 2010 at 9:45 pm
That is correct, but backup commands for SQL Server are issued in T-SQL, not through Windows.
July 20, 2010 at 9:06 am
Just to be absolutely sure I have asked this question the right way I want to ask it in this way.
1. Is there anyway I can issue TSQL commands in single user mode that will allow me to back up a database?
2. is single user mode always form the dos command line?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply