Script: Checking for DAC connections April 26th, 2010

Vinod Kumar

I hope you are all aware of the DAC (Dedicated Admin Connection) concept which we introduced with SQL Server 2005+ onwards. It is an awesome backdoor entry into SQL Server when things are not going good on the Server. It is an awesome way to troubleshoot SQL Server when it is unresponsive. As a DBA, you might want to know if anyone is using this special dedicated connection somewhere on your environment. Then here is a simple query that returns you this information …

SELECT t2.session_id, t1.name
FROM sys.tcp_endpoints AS t1
JOIN sys.dm_exec_sessions as t2
    ON t1.endpoint_id = t2.endpoint_id
WHERE t1.name like ‘Dedicated Admin Connection’

Share this article

Tags: , , , , , ,

This entry was posted on Monday, April 26th, 2010 at 04:35 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


6 Responses to “Script: Checking for DAC connections”

  1. student says:

    Great information! I’ve been looking for something like this for a while now. Thanks!

  2. Great information! I’ve been looking for something like this for a while now. Thanks!

  3. dental hygienist says:

    This is such a great resource that you are providing and you give it away for free. I enjoy seeing websites that understand the value of providing a prime resource for free. I truly loved reading your post. Thanks!

  4. CNA Practice Test says:

    Terrific work! This is the type of information that should be shared around the web.

  5. Great site. A lot of useful information here. I’m sending it to some friends!

  6. MarkSpizer says:

    great post as usual!

Leave a Reply



 

Email
Print