The SQL dedicated administrator connection(DAC) has been around since SQL 2005 and is a diagnostic connection that is designed to allow an administrator to troubleshoot SQL Server when the instance is having problems. Only members of the sysadmin role can connect using the DAC. You can connect using the DAC both through SQLCMD (SQLCMD –A –d master) and in SQL Server Management Studio (ADMIN:<instance_name>).
If you don’t have remote admin connections enabled in the instance, you will only be able to connect to DAC while logged into the server. It’s pretty simple to enable the setting. Just run this:
EXEC sp_configure ‘remote Admin Connections’, 1
While you can run many T-SQL statements while connected using DAC, there are limitations. For instance, you can’t run backup or restore commands while connected using DAC. You should stick to queries that check DMV’s such as sys.dm_tran_locks , sys.dm_os_scheduler, sys.dm_exec_requests, sys.dm_exec_sessions as well as some DBCC statements such as DBCC FREEPROCCACHE and DBCC SQLPERF. More on the DAC and its limitations here: https://msdn.microsoft.com/en-us/library/ms189595.aspx
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.