Hi Friends, Many of you would have read about or used DAC (Dedicated Administrator Connection). Introduced in SQL Server 2005, this is a special purpose connection only to be used in extreme connections when you are not able to connect to SQL Server by any other means. You can use this technique to troubleshoot and diagnose issues. However, to conserve resources, not every command can run under DAC mode. For example, BACKUP/RESTORE statements cannot run. You can run your DMVs to collect information such as sys.dm_tran_locks for the locking status, sys.dm_os_memory_cache_counters to check the health of caches, and sys.dm_exec_requests and sys.dm_exec_sessions for active sessions and requests, etc. SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon Database Engine startup. You can establish a DAC connection using sqlcmd; use the –A switch; and using SSMS; prefix the instance name with “ADMIN:” (without the quotes) – so if your instance name is SERVER\sql2k8r2, it becomes ADMIN:SERVER\sql2k8r2. There is only one DAC connection allowed per instance. Which means, if an admin is already connect using DAC, another attempt to connect with DAC will result in an error. So how do you find out if a DAC connection is already in use? If you are not able to connect using DAC, there is a possibility that someone has remotely connected using DAC (provided Remote connections are configured). Use the following script to find the SPID and all the sessions details that is using DAC:

SELECT s.*, e.*
FROM sys.tcp_endpoints as e JOIN sys.dm_exec_sessions as s
ON e.endpoint_id = s.endpoint_id
WHERE e.name='Dedicated Admin Connection'