unable to open SSMS in single user mode

  • KoldCoffee

    SSCoach

    Points: 18971

    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.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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.
  • KoldCoffee

    SSCoach

    Points: 18971

    So at this point I am limited to using only the cmd line until I can get the underlying problem solved?

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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.
  • KoldCoffee

    SSCoach

    Points: 18971

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

    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.

  • KoldCoffee

    SSCoach

    Points: 18971

    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:

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

    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.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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.
  • KoldCoffee

    SSCoach

    Points: 18971

    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!

  • KoldCoffee

    SSCoach

    Points: 18971

    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?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

    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.

  • KoldCoffee

    SSCoach

    Points: 18971

    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."

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

    That is correct, but backup commands for SQL Server are issued in T-SQL, not through Windows.

  • KoldCoffee

    SSCoach

    Points: 18971

    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 20 total)

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