Single-user mode connection and troubleshooting

Scenario : This is a named instance that needs to go into single-user mode for maintenance.

First, launch an instance

We will start directly using the Command line window. Reference article: "The remedy for forgetting the administrator password" http://jimshu.blog.51cto.com/3171847/1563207

1. Find the path to the Sqlservr.exe

Locate the named instance in Windows Services and find the path to the executable file. For example:

"C:\Program Files\Microsoft SQL Server\mssql10_50.sql2008r2\mssql\binn\sqlservr.exe"-ssql2008r2

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/58/35/wKioL1SsnQvyfkXYAAMykCpd-lk127.jpg "title=" The path to the executable file. png "alt=" wkiol1ssnqvyfkxyaamykcpd-lk127.jpg "/>

2. Launch an instance

Opens a command-line window. Copy the path of the executable to this command-line window, and then add the "-M" sqlcmd "parameter, specify single-user mode startup and only sqlcmd can connect to this instance.

C:\users\administrator> "C:\Program Files\Microsoft SQL server\mssql10_50.sql2008 R2\mssql\binn\sqlservr.exe"- SSQL2008R2- M "sqlcmd"

Once started, this window will display a lot of startup information.

Note : To ensure that the startup account has sufficient permissions, it is recommended that you change to Local System startup. Please modify it in SQL Server Configuration Manager.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/58/3A/wKiom1SsqtuAvxaKAAIz0UKowfA670.jpg "title=" Start the account. png "alt=" wkiom1ssqtuavxakaaiz0ukowfa670.jpg "/>

Second, confirm the port configuration

1. Enable "Listen All"

In the previous steps, look for the port information in the command-line window. For example:

2015-01-07 10:42:49.09 server         server is listening On [' Any ' <ipv6> 49157]. 2015-01-07 10:42:49.09 Server         server is listening in [' Any ' <ipv4> 49157]. 2015-01-07 10:42:49.10 Server         Server local connection provider is ready to accept connection O n [\\.\pipe\sqllocal\sql2008r2]. 2015-01-07 10:42:49.11 Server         Server local connection provider is ready to accept connection O n [\\.\pipe\mssql$sql2008r2\sql\query]. 2015-01-07 10:42:49.11 Server         server is listening on [:: 1 <ipv6> 49163]. 2015-01-07 10:42:49.11 Server         server is listening on [127.0.0.1 <ipv4> 49163]. 2015-01-07 10:42:49.12 Server         Dedicated admin connection support is established for listening Locally on port 49163.

Note : Because each instance is configured differently, you will not necessarily see the exact same information in this example. "Server is listening on [' Any ' <ipv6> 49157]" means listen all and listen for a dynamic port.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/58/39/wKiom1SsoHOiESEFAAIX639s_QQ495.jpg "title=" Listen all. png "alt=" wkiom1ssohoiesefaaix639s_qq495.jpg "/>

Once "Listen All" is enabled, only the bottom column "IPALL" is active, and the other IP separate configurations are not valid.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/58/39/wKiom1SsoNWihqcVAAGyl1fKfVY642.jpg "title=" Listen All Ipall.png "alt=" Wkiom1ssonwihqcvaagyl1fkfvy642.jpg "/>

2. Do not enable "listen All"

The following information shows that only the 127.0.0.1 port is listening.

2015-01-07 11:03:53.76 Server is listening on [127.0.0.1 <ipv4> 49163]. 2015-01-07 11:03:53.77 Server Dedicated admin connection support is established for listening locally on port 49163.

In the configuration interface, only for 127.0.0.1 this IP, "active" and "enabled" are set to "yes".

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/58/39/wKiom1SsouuQ3_wRAAHLyePh2-M769.jpg "title=" Do not enable all listening. png "alt=" wkiom1ssouuq3_wraahlyeph2-m769.jpg "/>

3. Troubleshooting

If the IP address has been modified by natively, the wrong old IP address will be bound to the instance without the "Listen All" enabled, which will result in an error and cause the startup to fail. Please refer to "Modifying the IP address of SQL Server server" http://jimshu.blog.51cto.com/3171847/1120377

Third, Client connection

Because the previous steps allow only SQLCMD connections, you need to open a new command-line window. Use the "-S" parameter, followed by an IP address and a port number (separated by commas). For example:

C:\users\administrator> sqlcmd -s 127.0.0.1, 49163 1>

Once the connection is successful, you can refer to the "Remedy for forgetting administrator password" instructions for some maintenance operations.

If there are other issues with the client connection, refer to the SQL Server client connectivity issue http://jimshu.blog.51cto.com/3171847/1395199

Iv. Stopping instances

In the command-line window that launches the instance, use the ctrl-c or Ctrl-break key combination to stop the instance.

Does wish to shutdown SQL Server (y/n)? Y 2015-01-07 11:36:37.76 spid7s SQL Server shutdown due to ctrl-c or ctrl-break signal. This is a informational message only. No user action is required. 2015-01-07 11:36:37.76 spid7s SQL Trace is stopped due to server shutdown. Trace ID = ' 1 '. This is a informational message only; No user action is required.

Even, you can close this command-line window directly.

This article is from the "We have chased the MSSQL" blog, please be sure to keep this source http://jimshu.blog.51cto.com/3171847/1600070

Single-user mode connection and troubleshooting