DAC - Dedicated Administrator Connection Configuration, Connecting and diagnostic the server

The DAC uses a special reserved scheduler which has one thread for processing requests. This essentially means that SQL Server is keeping a backdoor open to processor resources available just for you.

SQL Server’s DAC is a special diagnostic connection that’s intended for administrators to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. By default SQL Server listens for DAC on TCP port 1434. However, you can configure this. If you have changed the default connection port, you can find the port number the DAC is listening on in the Error log.

You can use the DAC in two different ways. You can use it via the sqlcmd prompt or you can use it from SQL Server Management Studio (SSMS).

HOW TO ENABLE THE DAC FOR REMOTE CONNECTIONS AND CLUSTERS

By default the DAC is only enabled for accounts logged onto the local machine. For production servers, that means it only works for remote desktop sessions to non-clustered SQL Server instances. If your instance is clustered or if you’re connecting over TCP/IP, you’re out of luck unless you change one setting. That setting is ‘Remote Admin Connections’.

Enabling the DAC for remote connections

=======================================================

 EXEC sp_configure 'remote admin connections', 1;

 GO

 RECONFIGURE

 GO

======================================================

HINT : the DAC uses a Dedicated TCP Endpoint. It’s even endpoint #1. The DAC is ranked #1 by endpoints everywhere!

CONNECT

------------

sqlcmd prompt

SQLCMD –S [ServerName\InstanceName] –U [UserName] –P [Password] -d master –A ( -A = DAC )

sqlcmd -S MySQL Server -E -d master –A (-E = Trusted Connection )

Diagnostic or TroubleShooting

/* Returns one row per authenticated session on SQL Server */

SELECT * FROM sys.dm_exec_sessions 

/* Returns information about each request that is executing within SQL Server. */

SELECT * FROM sys.dm_exec_requests 

/* Returns information about currently active lock manager resources in SQL Server */

SELECT * FROM sys.dm_tran_locks

Note: Here some Information was collected from different open source websites kindly note it and thanks.