SQL Server Administration: How to connect using the Dedicated Administrator Connection
The SQL dedicated administrator connection (DAC) has been around since SQL 2005, and is a diagnostic connection 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
GO
RECONFIGURE
GO
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:
___________________________________________________________________________
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
|