DAC – Dedicated Administrator Connection

 

Use Command below to connect as DAC

SQLCMD -A

 

It is on by Default for local server.

For remote(via network) enable it by running query below

sp_configure 'remote admin connections'

 

For Clusters, recommended to enable remote admin connections as no local DAC available

 

---- Query to Find if DAC - Dedicated Admin Connection used -----   select s1.session_id, s1.program_name,s1.host_name,t1.name from sys.tcp_endpoints t1 join sys.dm_exec_sessions s1 on t1.endpoint_id = s1.endpoint_id where t1.name = 'Dedicated Admin Connection' --- End of the Query -----

--- Query to check the DAC ---- --- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) --- select * from sys.dm_os_schedulers

 

Usage:

when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection

• Use DAC sparingly for monitoring and Troubleshooting
  
• Connect to master database first
  
• Use lightweight queries.
  
• Not advised for long running queries like ( DBCC CHECKDB, DBREINDEX, INDEXDEFRAG )