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:

 

Amit Bansal
SQL MCM, SQL MVP
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

Does SQL Server NEWSEQUENTIALID function always generates sequential GUIDs?
SQL Server Disable Row Versioning - DISABLE_VERSIONING wait type