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.

trace_flag_7806_1

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'

trace_flag_7806_2

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


Posted

in

by

Comments

2 responses to “Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition”

  1. SQL-Articles » Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Si Le avatar
    Si Le

    Hi,

    After enable the trace 7806 on the SQL Express, I still could not see the results like yours. The sqlcmd results showed the same msg as indicated on your article, i.e.: HResult 0xFFFFFFF… no difference. Can you help me resolving this issue? Thank you very much.

Leave a Reply

Your email address will not be published. Required fields are marked *