Who has the DAC open?

Leave a comment

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

  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”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: