From time to time I hear of DBAs that had to restart the SQL Server service on a 2005 or 2008 server because it had stopped accepting new connections. The thing is that most times those servers did not have to be restarted. If the DBA had logged in via the Dedicated Administrator Connection (DAC) they would have been able to identify and either kill or track the progress of the offending query.

So what is the DAC? It is a special reserved connection that allows 1 connection from the local machine by appending “ADMIN:” to the beginning of the server or server\instance name. (Exact instructions are available here) The DAC is by default only available to clients connection from the machine that the instance is running on. There is a setting that allows people to connect over the network but in an emergency it is good to know who is using that connection. Forcing people to log onto the box to use the connection means you can see who might have the connection by looking at who is connected to the server. Remember you can’t connect to SQL Server to see who is connected if things are bad enough that the DAC is your only option.

The great part about the Dedicated Administrator Connection (DAC) is that it is always available for someone to connect. Even when normal connections to the server do not work the DAC is ready to accept a connection. Once connected via SSMS (Query Window not Object Explorer) or SQLCMD a DBA can quickly diagnose and resolve an issue.

We no longer have to blindly restart SQL, hoping that we are not setting up a situation where our most important database will be in recovery for minutes or hours.

We now have the tools to accurately diagnose a problem the first time it happens.

We just have to remember to use them.