By: Ashish Kumar Mehta | Updated: 2011-11-07 | Comments (1) | Related: 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.
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.
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.
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.
Next Steps
- If you are using SQL Express make sure you have enabled the Dedicated Administrator Connection. Also, it would be good idea to check your other instances of SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 to make sure it is enabled.
- If at any point the database engine is not allowing regular connections, then use this feature to troubleshoot issues without shutting down SQL Server
- Review these related tips:
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-11-07
