DAC (Dedicated Admin Connection) in SQL Server 2005
Posted by decipherinfosys on February 12, 2007
In versions prior to SQL Server 2005, if the server had maxed out on the resources, had a spinloop or maybe had corruption, it was impossible to get access to the server via remote desktop, Enterprise Manager, Query Analyzer, osql, third party tools etc. As a result, the last resort that most of the database developers/DBAs adopted was “REBOOT” 🙂 But that is not the holy grail. At times, SQL Server was able to do a checkpoint process and shutdown gracefully but at other instances, due to to a shutdown and subsequent start-up and recovery phase, corruption or AVs (Access Violations) could have resulted not to mention that the recovery process could be a long one depending upon the amount of work. In order to remedy this situation, in SQL Server 2005, one has a Dedicated Admin Connection option. This option (when configured) allows the developer/DBA to connect to the instance and issue T-SQL commands to troubleshoot the issue – maybe killing the offending process as a last resort.
Please note that by default, this option is available only from the client on the server. In order to enable the remote clients to use the DAC option, you can enable this option using the surface area configuration option:
This can also be done usinng sp_configure command with the “remote admin connections” option being set to 1.
In order to use this option, you can either access it from the command prompt by using the new sqlcmd utility or through the Management Studio by using the ADMIN: option when connecting to the instance. Example:
Using sqlcmd:
sqlcmd -A -d testDB -E -S testinstance\namedinstance
-A is for the DAC
-d is for the database
-E is for integrated security
-S is for the instance (in this example, a named instance)
Management Studio:
a) Connect using the “Database Engine Query” option.
b) Put “ADMIN:” before the “testinstance\namedinstance”
c) Provide the connection information and connect.
Once you connect using DAC, you can execute any T-SQL commands to troubleshoot the issue and find the offending process.
2 Responses to “DAC (Dedicated Admin Connection) in SQL Server 2005”
Sorry, the comment form is closed at this time.
Systems Engineering and RDBMS 
SQLCMD utility in SQL Server 2005 « Systems Engineering and RDBMS said
[…] Posted by decipherinfosys on December 8, 2007 In one of our previous blog post, we had covered some of the command line utilities in SQL Server 2005. One of such utilities is SQLCMD. This is a replacement for the osql and isql command line utilities that have existed since ages in SQL Server. Those are still available but will be deprecated in the future and it is recommended to use SQLCMD instead. We had also covered the usage of SQLCMD when we explained the DAC feature in SQL Server 2005. […]
Prevent connections from certain IP Address(es) « Systems Engineering and RDBMS said
[…] And if you do not have any current valid connection open, you can connect through DAC (Dedicated Admin Connection) and then execute the command from above. We had discussed DAC in one of our previous posts here. […]