Here’s a quick little snippet of T-SQL script that will hopefully prove handy to someone (and that I’ll end up googling at some point and find my own blog post).

Are you trying to determine who is currently using the dedicated admin connection (aka DAC) on a particular SQL Server? Did SSMS give you a funny error leading you to believe you might not have the DAC?

The query below will reveal which session is using the dedicated admin connection. If no rows are returned then no one is using the DAC on that particular SQL Server instance and it’s yours for the taking!

SELECT	e.name, s.session_id
		, s.host_name
		, s.login_name
FROM	sys.tcp_endpoints as e 
JOIN	sys.dm_exec_sessions as s	ON	e.endpoint_id = s.endpoint_id
WHERE	e.name='Dedicated Admin Connection'

Sometimes I wish I could make these blog posts much more complicated so they’d fill up more space but seriously that’s all there is to it.

Perhaps you’re wondering what the heck a dedicated admin connection is? Here’s the definition from Microsoft TechNet:

The DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections.

In my case it’s been a pretty handy way to flip a database from single-user mode back to multi-user.