Dedicated Administrator Console (DAC) - saved an important day for a DBA
Until recently I have just used Dedicated Administrator Console (DAC) to connect just to test whether the connection will be successful. You may be aware that only administrator will be able to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections.
Recently we have had such a situation where a particular SQL instance was unresponsive for current connections and not accepting any newer connections, it was a situation where every DBA will be at panic, I'm not exceptional and immediately I found that a particular SPID was blocking and generated lots of deadlocks from the application. OS team were waiting on call to restart the Services on the server and I was completely reluctant to restart SQL Server services, then DAC has came into mind to kill that offending SPIDs. So at the end DAC has saved important task to avoid the restart of SQL Server services, as I was more keen to analyze the system usage with DMVs and if you restart that will lose the information that we require for investigations.
To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name. Object Explorer cannot connect using the DAC. A seperate listen-on will be created using TCP protocol only, so its important to use or enable TCP/IP on that SQL instance. Also on the Clustered environment it is important to turn-on 'Remote Admin Connections' using Surface Area Configuration tool. Another advantage is all the processes within DAC will be executed on a seperate reserver memory area.
