Who has the DAC open?
2May 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”.
Thank you SO much for this! Your post helped me tremendously.
Great! That’s always awesome to hear.