SQL Server Performance

Using Dedicated Administrator Connection[DAC] - SQLServer 2005

Discussion in 'Getting Started' started by Gobira, Dec 4, 2009.

  1. Gobira New Member

    What is DAC?
    SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.Enable DAC : Execute the below syntax against Master database to enable DAC on SQL Instance SP_Configure 'Remote_Admin_Connections' ,1goReconfigurego When to Use?Whenever we have issues on connecting the Production Database due to high data load we can use the DAC connection to kill blocking sessions or run sql scripts to resolve the issue. The DAC connection can be used only for critical and rare scenarios to resolve the issue. Step 1: Open SQL Server Management Studio Step 2: Go to File tab à New à Database Engine Query
    Step3: Connect as --> ADMIN:Instance Name
    Step4: Execute the syntax --> Use Master -->go
    Step5: you are connected to the database Note: Only one DAC connection can be made for one SQL Instance. Also, SystemAdmin privilege should be required for the user to use the same.
    Ref: http://msdn.microsoft.com/en-us/library/ms189595.aspx
  2. satya Moderator

    Welcome to the forums.
    As you have defined DAC is a last resort to clear out any blocking/issues occuring on SQL Server instance, as a DBA if you are using that means you need to perform a thorough performance audit on the database platform.
    By default, the connection is only allowed from a client running on the server.
    Because the DAC exists solely for diagnosing server problems in rare circumstances, there are some restrictions on the connection:
    • To guarantee that there are resources available for the connection, only one DAC is allowed per instance of SQL Server.
    • The DAC initially attempts to connect to the default database associated with the login. After it is successfully connected, you can connect to the master database. If the default database is offline or otherwise not available, the connection will return error 4060. However, it will succeed if you override the default database to connect to the master database instead using the following command:

      sqlcmd –A –d master

      It is recommend that you connect to the master database with the DAC because master is guaranteed to be available if the instance of the Database Engine is started.
    Do not use the DAC to run resource-intensive queries (for example. a complex join on large table) or queries that may block. This helps prevent the DAC from compounding any existing server problems.
    To avoid potential blocking scenarios, if you have to run queries that may block, run the query under snapshot-based isolation levels if possible; otherwise, set the transaction isolation level to READ UNCOMMITTED and set the LOCK_TIMEOUT value to a short value such as 2000 milliseconds, or both. This will prevent the DAC session from getting blocked. However, depending on the state that the SQL Server is in, the DAC session might get blocked on a latch. You might be able to terminate the DAC session using CNTRL-C but it is not guaranteed. In that case, your only option may be to restart SQL Server.
    See http://sqlserver-qa.net/blogs/tools...ole-dac-saved-an-important-day-for-a-dba.aspx

Share This Page