SQLRx Tip of the Month
 
December 2015
______________________________________________________________________________
SQL 2008

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!
Be sure to check out our blog for more SQL Server information.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].


 

Phone: 214.526.7680 x 113

Toll free:  800.85.SQLRx  (x113)

blog.sqlrx.com

@SQLRx

Facebook
 


 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit


 

30 Years of Credibility & Trust


 

Join our email list          Check out our events