Dedicated Administrator Connection (DAC)

We all dread the scenario whereby SQL Server is under so much load and has a complete lack of resources that no further connections can be made.  Although extreme and a situation I have never been in there is a saving grace, one last chance before opting for measures that will induce outages, the Dedicated Administrator Connection or DAC.  

First introduced in SQL Server 2005 the DAC is a special connection that is accessible when all other connections fail. The DAC is available via both SQLCMD and SSMS however it is recommended to use SQLCMD as it uses fewer resources than the GUI of SSMS.  The sole purpose of the DAC is for diagnosing problems when no other connection can be made, it is not to be used as a regular connection.  It is also recommended that you connect to the master database when using the DAC and that you do not run any resource intensive queries.

There are some limitations which I will detail below;

1 - Only one connection to the DAC is allowed, if already in use any further connections will be rejected

The below query it will return the SPID for the DAC if it is in use;

SELECT  s.session_id
FROM    sys.tcp_endpoints AS E
        INNER JOIN sys.dm_exec_sessions AS S ON E.endpoint_id = S.endpoint_id
WHERE   E.name = 'Dedicated Admin Connection';

2 - By default the DAC is only available locally, this can be changed by enabling remote admin connections using sp_configure
3 - Only users with membership in the sysadmin fixed server role can connect to the DAC
4 - Some SQL Statements are unavailable using the DAC, for example BACKUP or RESTORE.

Below are some examples of connecting to the DAC using both SQLCMD and SSMS, If you have never connected to the DAC or used SQLCMD for that matter, I recommend testing connectivity to the DAC and becoming familiar with SQLCMD.  This will save precious time in the event of a serious problem, you really don't want to be googling or boling when facing a potential outage.

Example 1: Connect to the DAC using SQLCMD and integrated security

Open command prompt and run;

sqlcmd -S ServerNameHere\InstanceNameHere -d master -A

Example 2: Connect to the DAC using SQLCMD and SQL authentication

Open command prompt and run;

sqlcmd -S ServerNameHere\InstanceNameHere -U UserNameHere -P PasswordHere -d master -A

Example 3: Connect to the DAC using SSMS

Open SSMS, at the connection window prefix the ServerName or ServerName\InstanceName with ADMIN:

ADMIN:SQL365

or

ADMIN:SQL365\INST01

Enjoy!

Chris