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

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.

Leave a Reply

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. 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 *

*


x

Related Posts

SQL 2012 DTA Engine Crashes on Windows 8
Question: Some time back I wrote an article on the new feature that’s added to SQL Server 2012 DTA (database tuning advisor). I have installed S...
Day 14–Trace Flag 3505–Control SQL Server Checkpoint Behavior
What’s is checkpoint? Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from ...
Day 12–Trace Flag 1222–Capture Deadlock information in XML like format
Yesterday we saw the trace flag 1204 which will give you the information based on nodes. Today we are going to get similar deadlock information u...
powered by RelatedPosts