Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition

Dedicated administrator is one of the new feature introduced from SQL Server 2005. This is a wonderful feature for DBA’s to troubleshoot SQL Server when it goes unresponsive. For more on DAC check the article by Deepak. However this feature is not supported in SQL Express edition by default. So how can I utilize this feature in SQL Express edition?

Great now good news for you. Microsoft has added a trace flag 7806 to enable this feature in SQL Express edition so that now you can connect to your SQL express instance for diagnosis. Deepak has described different ways to connect as DAC, however I’m going to use –A parameter through SQLCMD. Let me connect to SQL Express edition without enabling the trace flag , I’m thrown with the error message below.

Now let me enable the trace flag 7806 in startup parameter (Refer my article “Enable \ Disable Trace flags in SQL Server” for enabling trace flag) and restart SQL Express edition so that we can utilize this feature in Express edition. From the image below you can see that now I’m able to connect using DAC, I have used the query below to find the DAC session ID.

SELECT s.session_id
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'

This trace flag works from SQL Server 2005 and above versions. You also need to make sure SQL Browser service is running.