SYSK 151: New in SQL 2005 – DAC (dedicated administrator connection)

  • Article

There are times when SQL Server may be so busy processing requests that it can no longer allocate memory or processor resources to even allow an administrator to connect. This was a big issue with SQL Server 2000 but SQL Server 2005 solves this problem by introducing a feature called "Dedicated Administrator Connection” (DAC)

 

DAC uses a specific TCP endpoint in a SQL Server instance that is always attached to a dedicated UMS (User Mode Scheduler). DAC provides a connection that could always be used by a member of the sysadmin role to access a SQL Server instance, thereby guaranteeing that an administrator could not be locked out of SQL Server due to resource allocation issues.

 

Any operation that would spawn multiple threads, such as a backup or restore, are not allowed within the DAC and also only a single connection at a time is allowed. If the connection is already being used, any subsequent connections are refused.

 

You can create a connection to DAC in only two ways:

 

1) Using SQLCMD

2) Using SQL Server Management Studio

 

*You must use -A parameter when using SQLCMD to use Dedicated Administrator Connection.

 

Example using SQLCMD:

 

C:\> SQLCMD -S ServerName -E –A

 

The administrator can now execute queries to diagnose the problem and possibly terminate the unresponsive sessions.

 

Special thanks to Saleem Hakani for creating and permitting me to publish this post!