A dedicated administrator connections DAC is the last resort for a DBA when trying to fix an instance where no new connections are possible anymore because its resources are exhausted. For such situations, SQL server always retains a limited chunk of memory and other necessary resources to allow a last connection: the dedicated administrator connection. A DAC is also required to select from the system base tables. Connect with Management Studio With Management Studio , a dedicated administrator connection cannot be opened with the usual connection method. The following error is thrown. A dedicated administrator connection needs to be established with a Database Engine Query. When the connection dialog opens, the server name needs to be prefixed with admin: :.
How to enable DAC for remote connections
You would be able to see the connection name at the bottom of the query window as shown in the snippet below. Then, you would end up receiving below mentioned error message. Once you have established a successful DAC connection you can execute troubleshooting queries to identify the bottleneck and take action accordingly. This feature can help you troubleshoot issue rather than just restarting SQL Services or rebooting servers remotely. Chetna Bhalla, the founder of MyTechMantra. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas.
At first I started to tell him, but figured it would be smarter to document the options and share the information. This tips shows you how you can enable this feature. It's a good practice to enable the DAC feature once SQL Server is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server and later versions. This can be done by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the snippet below. This will open up View Facets window as shown in the snippet below. NOTE: The admin: string is case insensitive. Thank you for the post.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search.