Archive for April 26th, 2010

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’

Continue reading...