Use of the SQL Server Administrator private Connection

As a DBA, you often deal with some of the more difficult services that are not responding. Given the seriousness of the situation, most DBAs may use the "restart" Dafa directly to restore the normal operation of the production environment as quickly as possible, but in most cases we cannot investigate the cause, even if a dump file is generated, without Microsoft's support, We were also kept in the dark, and luckily Microsoft left us a "fallback" for us to use when the SQL service could not connect and see "cause", that is DAC, dedicated administrator connection, administrator-specific connection.

For administrator-specific connections, see: http://msdn.microsoft.com/zh-cn/library/ms189595.aspx

To turn on remote administration connections: This option is turned off by default and can only be connected to the server if it is not turned on.

SELECT * from sys.configurations WHERE name = ' Remote admin connections '

To view and use the following statement to open

sp_configure ' remote admin connections ', 1

Go

Reconfigure

Go

How do I connect?

1, through the SQLCMD connection:

Sqlcmd-s. A

2, you can also bind admin to the front of the server:

Sqlcmd-sadmin:.

3, in sqlcmd display results are always difficult to see, so I prefer to use the Query Editor to start:

First disconnect all connections in Object Explorer, and then click New Query and ENTER: admin: Server name

4, after entering the back door of SQL Server, we can through some of the DMV and so on to see what the current process is doing, are waiting for what, so that further analysis to determine where the problem.

It is recommended that the production environment turn on this option to facilitate troubleshooting when SQL Server is not responding.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/