none
SQL error 17113 while starting the the Server in Single User mode RRS feed

  • Question

  • I am running into a strange error while trying to perform a master database restore.

    While trying to start the Server in Single user mode I get a error 17113, the Server stas in multi user mode as expected, but using the command line sqlservr.exe –m –s SERVER\SQL1 I get the Empty message box, setting single user mode -m in the  Startup Parameters for SQL Server also fails with the following message in the sql log

    2013-04-12 18:33:47.46 Server      Registry startup parameters:
      -m  -dE:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf
      -e E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG
      -l E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\mastlog.ldf
    2013-04-12 18:33:47.46 Server      Error: 17113, Severity: 16, State: 1.
    2013-04-12 18:33:47.46 Server      Error 3(failed to retrieve text for this error. Reason: 15100) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

    The -f option gives similar behaviour, the SQL Server is a fresh install over a previouly uninstalled SQL Server.

    Friday, April 12, 2013 2:39 PM

Answers

  • The registry startup parameters are sensitive to extra spaces, and so the reported parameters in the log are not necessarily the ones you are actually using. And the UI for setting them before SQL 2012 is horrible and allowed you to insert hard-to-find extraneous spaces.

    To start the SQL Service in single user mode, but not in the console window use the /m switch on NET START:

        net start mssqlserver /m

    You can also restrict the client app name to ensure that you can be the one user:

       net start mssqlserver /mSQLCMD

    Will start the service in single user mode, and only accept connections from the SQLCMD program.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Friday, April 12, 2013 3:27 PM
  • *  -dE:*\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf

    *  -d E:*\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf

    The only difference I see is in the log: A (missing) space Parameter -d and the file Name; but even with this it should work. Very strange.

    No, that is the very issue. When you see something like that in the SQL Server error log, you know that the option is not interpreted as intended. This is how it looks in the errorlog on my instance:

    2013-04-12 23:52:50.97 Server      Registry startup parameters:
        -m
        -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
        -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
        -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Note that every option is on its own line. David did something wrong when he entered the -m option. Maybe he forgot to add a semicolon to the option, so that -d and everthing that follows it became an argument to -m and then it went downhill from there.

    The dialog for adding startup parameters in SQL 2005 and SQL 2008 is very error-prone as you easily can miss a semicolon, and a space too many or whatever. And reading the full string is difficult. Thankfully, the SQL 2012 Configuration Manager, there is a dialog similar to what we had in SQL 2000.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 12, 2013 9:57 PM

All replies

  • 2013-04-12 18:33:47.46 Server      Error: 17113, Severity: 16, State: 1.
    2013-04-12 18:33:47.46 Server      Error 3(failed to retrieve text for this error. Reason: 15100) occurred while opening file 'master.mdf' to obtain

    Hello,

    Windows Error Code 3 means "Path not found". Have you checked the files/Folders from log, if they are the correct ones, they exists and if they are accessable?


    Olaf Helper

    Blog Xing

    Friday, April 12, 2013 3:12 PM
    Moderator
  • Hi,

    Yes I did check, The files look correct, they seem to work fine in Multi User Mode. This the equivalent log entry for the Normal (Multi User) start up

    2013-04-12 22:15:21.86 Server      Registry startup parameters:
      -d E:\SQL_Data\SQL1\MSSQL10_50.APS_SQL1\MSSQL\DATA\master.mdf
      -e E:\SQL_Data\SQL1\MSSQL10_50.APS_SQL1\MSSQL\Log\ERRORLOG
      -l E:\SQL_Data\SQL1\MSSQL10_50.APS_SQL1\MSSQL\DATA\mastlog.ldf
    2013-04-12 22:15:21.87 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    I am also getting problems starting from the Command prompt using the Startup Parameters from the SQL Services Configuration.

    David Hodkinson

     

    Friday, April 12, 2013 3:24 PM
  • The registry startup parameters are sensitive to extra spaces, and so the reported parameters in the log are not necessarily the ones you are actually using. And the UI for setting them before SQL 2012 is horrible and allowed you to insert hard-to-find extraneous spaces.

    To start the SQL Service in single user mode, but not in the console window use the /m switch on NET START:

        net start mssqlserver /m

    You can also restrict the client app name to ensure that you can be the one user:

       net start mssqlserver /mSQLCMD

    Will start the service in single user mode, and only accept connections from the SQLCMD program.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Friday, April 12, 2013 3:27 PM
  • 2013-04-12 22:15:21.86 Server      Registry startup parameters:
      -d E:\SQL_Data\SQL1\MSSQL10_50.APS_SQL1\MSSQL\DATA\master.mdf
      -e E:\SQL_Data\SQL1\MSSQL10_50.APS_SQL1\MSSQL\Log\ERRORLOG
      -l E:\SQL_Data\SQL1\MSSQL10_50.APS_SQL1\MSSQL\DATA\mastlog.ldf

    Hello David,

    If I compare your last log above with your first log

    2013-04-12 18:33:47.46 Server Registry startup parameters: -m 
    -dE:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf
    -e E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG
    -l E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\mastlog.ldf

    then I would say, the Folders are different, isn't it?


    Olaf Helper

    Blog Xing

    Friday, April 12, 2013 3:29 PM
    Moderator
  • Hi Olaf,

    Well spotted, but it is not the problem,  it should read

    2013-04-12 18:33:47.46 Server      Registry startup parameters:
      -m  -dE:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf
      -e E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG
      -l E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\mastlog.ldf
    2013-04-12 18:33:47.46 Server      Error: 17113, Severity: 16, State: 1.
    2013-04-12 18:33:47.46 Server      Error 3(failed to retrieve text for this error. Reason: 15100) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

    compared to

    2013-04-12 22:15:21.86 Server      Registry startup parameters:
      -d E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf
      -e E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG
      -l E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\mastlog.ldf
    2013-04-12 22:15:21.87 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    David  

    Friday, April 12, 2013 3:41 PM
  • Hi David,

    That worked,

    2013-04-12 23:46:58.86 Server      Registry startup parameters:
      -d E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf
      -e E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG
      -l E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\mastlog.ldf
    2013-04-12 23:46:58.86 Server      Command Line Startup Parameters:
      /m
    2013-04-12 23:46:58.88 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    But why should NET START work but not from the command prompt or the Services start up? I have used that method before on a earlier instalation of SQL 2008 R2 on the same machine.

    Thanks

    David

    Friday, April 12, 2013 3:54 PM
  •   -dE:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf

      -d E:\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf


    The only difference I see is in the log: A (missing) space Parameter -d and the file Name; but even with this it should work. Very strange.

    Olaf Helper

    Blog Xing

    Friday, April 12, 2013 3:57 PM
    Moderator
  • Yes.  I've edited the earlier reply with the reason: the registry parameter parsing code is fragile and picky, and the UI allows you to enter invalid parameters, and the log reports malformed parameters, but SQL doesn't actually use them.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, April 12, 2013 4:00 PM
  • Hi Olaf,

    the database, log and error log parameters were created during the instalation, I only added the -m to them.

    It starts and stops with the NET START and NET STOP commands, but now I cannot connect to the instance, it say only one administrator can connect, but I am the only administrator and its a dedicated test machine.

    David

    Friday, April 12, 2013 4:11 PM
  • That's what the /mSQLCMD is for.  You can start it and ensure that you will be able to connect using SQLCMD.  You can use any application name here and set it in your connection string.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, April 12, 2013 4:16 PM
  • Other Option is try to use a (local) DAC = "dedicated admin connection" with SqlCmd; see Using a Dedicated Administrator Connection

    Olaf Helper

    Blog Xing

    Friday, April 12, 2013 4:26 PM
    Moderator
  • *  -dE:*\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf

    *  -d E:*\SQL_Data\SQL1\MSSQL10_50.SQL1\MSSQL\DATA\master.mdf

    The only difference I see is in the log: A (missing) space Parameter -d and the file Name; but even with this it should work. Very strange.

    No, that is the very issue. When you see something like that in the SQL Server error log, you know that the option is not interpreted as intended. This is how it looks in the errorlog on my instance:

    2013-04-12 23:52:50.97 Server      Registry startup parameters:
        -m
        -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
        -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
        -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Note that every option is on its own line. David did something wrong when he entered the -m option. Maybe he forgot to add a semicolon to the option, so that -d and everthing that follows it became an argument to -m and then it went downhill from there.

    The dialog for adding startup parameters in SQL 2005 and SQL 2008 is very error-prone as you easily can miss a semicolon, and a space too many or whatever. And reading the full string is difficult. Thankfully, the SQL 2012 Configuration Manager, there is a dialog similar to what we had in SQL 2000.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 12, 2013 9:57 PM