Who has the DAC open?

, 2014-06-05 (first published: )

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads