Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enabling Dedicated Administrator Connection in SQL Server 2008 Express Edition


By:   |   Last Updated: 2011-11-07   |   Comments (1)   |   Related Tips: More > Dedicated Administrator Connection

Problem

One very nice feature that was introduced in SQL Server 2005 was the Dedicated Administrator Connection (DAC).  This gives you the option to always have a way to connect to SQL Server if SQL Server is unresponsive.  In the Standard and Enterprise versions of SQL Server this is enabled by default, but you have to make some configuration changes for this to work for SQL Server Express.  In this tip I walk through how to set this up for SQL Server 2008 R2 Express, but the same steps can be followed for SQL 2005 Express and SQL 2008 Express.

Solution

In SQL Server 2005, Microsoft introduced a new feature called Dedicated Administrator Connection (DAC). Using this feature a SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario, a DBA can connect to the SQL Server Instance to troubleshoot and kill any SQL Server processes that are causing issues. The DAC allows database administrators to connect to a SQL Server Instance and execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems.

In SQL Server Standard and higher versions this is enabled by default, but this is not the case for SQL Server Express.

In addition, the remote Dedicated Administrator Connection feature is disabled by default. This allows you to connect from a remote machine using the DAC.  It's a good practice to enable the remote DAC feature once SQL Server is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. If you want to enable the remote Dedicated Administrative Connection I recommend you read this tip Enable SQL Server 2008 Dedicated Administrator Connection. Also note that only one dedicated administrator connection is allowed at a time.

Enabling DAC for SQL Server Express

As mentioned this is not turned on by default for SQL Server Express, so let's go through the steps you need to follow to enable the Dedicated Administrative Connection (DAC) in SQL Server Express Edition.

1. Click Start -> Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager

2. In SQL Server Configuration Manager, on the left pane click SQL Server Services and then right click SQL Server (SQLEXPRESS) service and select Properties from the drop down list in the right pane as shown in the below snippet.

sql server configuration manager

3. In SQL Server (SQLEXPRESS) Properties window click on the Advanced Tab as shown in the below snippet.

Under Startup Parameters as highlighted below you need to add the 7806 Trace Flag as ";-T7806".  Note that each startup parameter is separated using a semicolon, so for this entry we are adding ;-T7806 as the last startup parameter. Click OK to save the changes and then restart the SQL Server Express Service to reflect the changes.

sql server express startup propertes

5. Once Trace Flag 7806 is added successfully and the service is restarted you will be able to get connected to SQL Server Express Edition using the Dedicated Administator Connection and run queries to troubleshoot performance issues as shown below using SQLCMD which is one method to use the DAC.

sqlcmd command line

6. To validate this is setup, you can query the SQL Server Error Log and you should see an entry "Dedicated admin connection support was established for listening locally on port". You can execute the below TSQL code to verify that the entry exists in the SQL Server Error Log.

USE master
GO
xp_readerrorlog 0, 1
, N'Dedicated admin connection support was established for listening locally on port'
, NULL
, NULL
, N'asc' 
GO

And here is the output from the query showing the port that is being used.

dedidcated admin connection setup

Next Steps


Last Updated: 2011-11-07


get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, November 07, 2011 - 1:33:06 PM - PaulSaid Back To Top

This is a nice little tip to know about.

My company has a large number of Express instances.


Learn more about SQL Server tools