THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

Trace flags - TF 7806

The trace flag 7806 is necessary when a DBA would like to use the dedicated administrator connection (DAC) in the SQL Server Express. The DAC is turned off when you install the SQL Server but it is a good practice to turn it on after the installation is done. The DBA will really need the have access to the unresponsive database server and having DAC active he/she has more chance to do the work. However this feature is not supported in the SQL Server Express edition by default. 

Microsoft has added a trace flag 7806 to enable this feature in the SQL Server Express.

As the flag is a global flag we have to turn it on the service level. You should add a parameter –T7806 to the parameter list and restart the service.

After you did that you are now able to use the DAC in the SQL Server Express. For example you could try to connect using sqlcmd tool:

Sqlcmd –S localhost\sqlexpress –E –A

I made an assumption that on your local server there is an instance of the SQL Server Express which is called "sqlexpress" and you  connect to this instance using your Windows credentials

When you are connected try to run that query:

SELECT S.session_id FROM sys.tcp_endpoints as E JOIN sys.dm_exec_connections as S

On E.endpoint_id = S.endpoint_id

WHERE E.name = ‘Dedicated Admin Connection’

 

Last remarks – the DAC can be used only by sysadmins

Published Saturday, August 30, 2014 3:51 AM by Damian
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

pete said:

Thanks. I was really surprised once I found that I have to turn the fleag in Express :)

August 30, 2014 5:08 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement