Dedicated connection DAC for SQL Server database administrator

Source: http://www.cnblogs.com/lyhabc/archive/2012/09/23/2698702.html

DAC: dedicated Admin Connection

When SQL Server is unable to establish a database connection due to insufficient system resources or other exceptions, you can use the system-reserved DAC to connect to the database for troubleshooting and troubleshooting. A DAC can use only limited resources. Do not use a DAC to run a resource-intensive query, or you may have serious blocking.

How to enable the DAC feature dedicated Administrator Connection feature and considerations

1. Only the system administrator (sysadmin) role member can use the DAC to connect to the SQL Server (local) on-premises connection

2. There can only be one DAC in a single execute.

3. Using a DAC is often a way for DBAs to look up and troubleshoot SQL Server problems (when it's not normal to connect to a single body),

4, like perform Sp_who2, Kill SPID, DBCC SQLPERF, DBCC dropcleanbuffers ... When using the DAC connection, do not perform commands that consume a lot of resources, such as DBCC CHECKDB, DBCC SHRINKDATABASE. such as

5, Use the DAC login to modify the system table or view the system table, you can modify the system table before SQL2000, to the SQL2005 began to limit you

SQL to open DAC

1 Use master         2 Go         3 sp_configure ' show advanced options ', 1  4 GO     5 sp_configure ' remote admin connections ' , 1  6 go         7 RECONFIGURE with OVERRIDE     8 Go 9 all SELECT * from  sys.configurations WHERE name = ' Remote AD Min Connections '

Dedicated connection DAC for SQL Server database administrator