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

1. Someone is connected as the DAC
2. 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”.

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication Tagged: DAC, microsoft sql server, problem resolution