Published on

April 15, 2008

Exploring the Dedicated Administrator Connection in SQL Server

SQL Server is a powerful database management system that allows administrators to perform various tasks and resolve issues. One of the features introduced in SQL Server 2005 is the Dedicated Administrator Connection (DAC), which provides a guaranteed connection to the SQL Server instance, even when it is unresponsive.

The DAC can be accessed using the SQLCMD command line tool or through Management Studio. By using the special administrator switch (-A) with SQLCMD or connecting to “admin:instance” in Management Studio, a member of the sysadmin role can connect to the server and perform diagnostic functions or execute TSQL statements to resolve problems.

There are a few restrictions when using the DAC. Only one DAC connection is allowed at a time, and the DAC will attempt to connect to the default database, which may generate an error if the database is unavailable. To force a connection to the master database, the command “sqlcmd -A -d master” can be used. It is also important to note that parallel queries or commands like RESTORE or BACKUP cannot be executed through the DAC.

When using the DAC, it is important to be mindful of the limited resources available. Running resource-intensive queries can further strain an already struggling SQL instance. Additionally, the DAC is not available in the Express Edition without a trace flag. To enable remote DAC connections, the “sp_configure” command needs to be executed with the remote admin connections option.

The DAC provides a range of commands that can be used for diagnostic purposes. These include querying system tables to identify blocks and suspect processes, using DBCC commands to free resources and check the health of databases, and even the ability to use the KILL command to terminate specific sessions.

Here is an example of how to use the DAC:

Open a Command Prompt window and start your SQL instance and SQL Browser service. Execute the following command:
sqlcmd -S yourservername -U sa -P yourpassword -A

You will be presented with a prompt. Enter the following command to show all processes running on the server:
select Session_id, login_time, cpu_time, memory_usage, reads, writes, login_name from sys.dm_exec_sessions WITH (NOLOCK)
GO

The results may not be displayed clearly in the Command Prompt window. To view the results in a more readable format, open Management Studio and connect to “admin:instance” using a sa account. Execute the same command in the new query window, and the results will be neatly formatted.

In conclusion, the Dedicated Administrator Connection in SQL Server provides administrators with a reliable way to diagnose and resolve critical problems without the need for a reboot or restarting the SQL Server instance. It is a valuable tool for experienced DBAs and can greatly simplify troubleshooting processes.

References:
Books Online – Using a Dedicated Administrator Connection (URL)

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.