Dedicated Administrator Connection

Dedicated Administrator Connection

     It is rare, but SQL Server can stop responding to normal queries and appear to be frozen. How could this happen? Consider that when you bought the system that houses SQL Server, you likely planned for future growth and usage. No matter how much time and effort you put into this plan, though, some companies just outgrow their servers too quickly, and the servers can’t handle the workload. In a scenario like this, it is possible that SQL Server might stop responding to normal queries. To troubleshoot this issue, or any kind of problem where SQL Server just isn’t responding to normal calls, you need the Dedicated Administrator Connection (DAC).

     The DAC is a special diagnostics connection that is always available for connection, even under normal operating circumstances. As the name implies, only administrators (members of the sysadmin server role) can connect to the DAC to run diagnostic queries and troubleshoot problems. So, how does it work?

     By default, SQL Server listens for normal queries on TCP port 1433, so when a user runs a SELECT query, it is transmitted to the server over port 1433. The DAC listens, by default, on TCP port 1434, so it is not cluttered with user traffic. In other words, it is always free and available for connections. Because the DAC is always available, it always consumes some sys- tem resources, but these are kept to a minimum.

     Because the DAC consumes minimal resources and is meant to be used only for diagnostic functions, some limitations exist on what you can do with it:

1. To ensure available resources, only one DAC connection is allowed per server. If you try to open a DAC connection to a server that already has a DAC connection open, you will get error 17810, and the connection will be denied.
2. You can’t run any parallel commands or queries from within the DAC. For example, you cannot run BACKUP or RESTORE.
3. Because of resource constraints, the DAC is not available in SQL Server 2005 Express Edition.
4. Because of the limited resources available, you should not run complex queries in the DAC, such as large queries with complex joins.
5. By default, you cannot access the DAC from a remote machine; you must be on the server. You can change this by setting the remote admin connections option to 1 by using the sp_configure stored procedure, like this:

sp_configure ‘remote admin connections’, 1

GO

RECONFIGURE

GO

   

That seems like a lot of restrictions, so you may be wondering what you are allowed to do. The

DAC is especially good for these tasks:

·         Querying dynamic management views:

·         You can query sys.dm_exec_requests to find blocking queries.

·         You can query sys.dm_os_memory_cache_counters to check the health of the system memory cache. [1] You can query sys.dm_exec_sessions for information about active sessions.

•  Querying catalog views

Running basic DBCC commands:

·      You can use DBCC FREEPROCCACHE to remove all elements from the procedure cache.

·    You can use DBCC FREESYSTEMCACHE to remove all unused entries from all caches.

·      You can use DBCC DROPCLEANBUFFERS to remove all clean buffers from the buffer  pool.

·      You can use DBCC SQLPERF to retrieve statistics about how the transaction log space is used in all databases.

•  Using the KILL command to end an errant session

               You can see that this is a powerful weapon in your troubleshooting arsenal, but how do you use it? In this task, you will work with the DAC.