Using DAC with SQLCMD Command Line Utility
It is advised to use the SQLCMD command line utility to use the DAC feature, especially when SQL Server is facing high CPU or Memory utilization issues. The reason to use SQLCMD is because it is a light weight command line utility and it uses very little server resources such as Memory and CPU when connected locally or remotely. In scenarios when the server is not responding to standard SQL Server connections this is the best approach. You need to be a member of sysadmin fixed server role to connect and use the DAC. The (-A) is the special administrator switch which needs to be used when connecting to a SQL Server 2005 or aSQL Server 2008 instance using DAC with the SQLCMD command line utility.
SQLCMD –S AKMEHTA –U UserA –P UserA$ –A
Explanation for Command Line Options which we have used in SQLCMD:
-S <Provide SQL Server Instance>
-U <User Name>
-P <Password>
-A Logs in to SQL Server with a Dedicated Administrator Connection (DAC).
Once connected using SQLCMD, database administrators can use the SQL Server Diagnosis queries to troubleshoot performance issues.
- Query Dynamic Management Views (DMV) like sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_requests etc
- Execute Basic DBCC Commands like DBCC SQLPERF(LOGSPACE), DBCC DROPCLEANBUFFERS etc
- Run KILL ‘SPID’ etc
Using DAC with SQL Server Management Studio
1. In SQL Server Management Studio, Press CTRL + N or click Database Engine Query
2. In Connect to Server dialog box, type ADMIN: followed by the name of SQL Server Instance in the Server name textbox. You can see that in the below example to get connect to a SQL Server instance named AKMEHTA, we have provided the Server name value as ADMIN:AKMEHTA
3. In the Authentication drop down list, there will be two options, namely Windows Authentication and SQL Server Authentication. In this example I will be using SQL Server Authentication. I have provided the credentials of a member of the sysadmin group and then clicked Connect to establish the connection using DAC.
4. If there is no other dedicated administrator connection in use then the attempt will be successful. Otherwise the connection will fail with an error indicating it cannot establish the connection.
5. In the new query window which has opened up, you can type the queries below which will help you quickly diagnosis performance issues.
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_requests
Limitations when using DAC
- Only one DAC connection is allowed per instance of SQL Server. This limitation is there in both SQL Server 2005 and SQL Server 2008
- You will be receive the error below when a user tries to connect using DAC and another DAC connection is active
Could not connect because the maximum number of dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.
- It is not possible to take a database backup or restore a database when you have connected using a DAC
- It is advised not to run resource intensive queries when connected to an SQL Server Instance using a DAC
- You need to be a member of the ysadmin fixed server role to use a DAC
- It is likely that if your database engine is running you will be able to access master database and then diagnosis the performance issues on the SQL Server
Conclusion
The Dedicated Administrator Connection feature which was introduced in SQL Server 2005 is a great help for Database Administrators especially in scenarios when SQL Server is not responding to standard user connections. Using the DAC, an administrator can get access to an instance of SQL Server and execute queries to diagnose performance issues.
]]>