May 28, 2014 by Kenneth Fisher
The other day I started seeing the following error in my SQL log.
Error: 17810, Severity: 20, State: 2.
Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
Now this disturbed me somewhat. It means that
- Someone is connected as the DAC
- The same someone or someone else is trying to connect to the DAC and failing
This connection is specifically for diagnostic queries when there are problems. It has a number of factors that make it “Not for general usage”. Including the facts that there can only be one of these connections at a time and it has it’s own (rather limited) resources. This means I need to track down who is connecting to the DAC or they may be connected to it when I actually need it. I’ll need to create an extended event session to track them down over time but for now I want to be able to tell who has a DAC connection open currently.
SELECT * FROM sys.dm_exec_sessions WHERE endpoint_id = 1
The endpoint #1 is the DAC. You can see this in the system view sys.endpoints. Sys.dm_exec_sessions has a number of useful pieces of information that can lead us back to the culprit. Among others you will find the host (host_name), the application (program_name), the login (login_name) and the NT login if there is one (nt_domain and nt_user_name). This information is usually more than enough to find or at least start down the path of finding “who done it”.