Basics of Dedicated Administrator Connection (DAC) in SQL Server

SQLServerF1

What is Dedicated Administrator Connection (DAC) in SQL Server?
Microsoft SQL Server gives a Dedicated Admin Connection (DAC). The DAC permits a head to get to a running case of SQL Server Database Engine to investigate issues on the server—notwithstanding when the server is lethargic to other customer associations. The DAC is accessible through the sqlcmd utility and SQL Server Management Studio. The association is just permitted from a customer running on the server. No system associations are allowed.

To utilize SQL Server Management Studio with the DAC, unite with a case of the SQL Server Database Engine with Query Editor by writing ADMIN: before the server name. Object Explorer can’t join utilizing the DA

How to Enable Dedicated Administrator Connection (DAC) for SQL Server instance?
To enable DAC, Connect to the instance and open a new query window, run the following command

sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;
GO

How to verify if Dedicated Administrator Connection (DAC) is enabled on a SQL Server Instance?
We can verify or confirm that Dedicated Administrator Connection (DAC) is working by connecting to SQL Server instance using the following in the “Instance” field of SSMS login window

ADMIN:

How many Dedicated Administrator Connections (DAC) can be opened at a time for a particular SQL Server instance?
Only one connection can be made using Dedicated Administrator Connections (DAC).

When is the Dedicated Administrator Connections (DAC) to be used?
Dedicated Administrator Connections (DAC) can be used in cases where SQL Server is running but not responding to any regular connections due to resource usage. DAC connection has its own memory and scheduler and dedicated thread, which allows connection through DAC to run queries even when the SQL Server is not responding. It is not advisable to run a large or complex queries with DAC, rather it is to be used to identify the resource consuming queries causing SQL Server to hang or to not respond and kill any such offending sessions.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Leave a Reply

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