What is DAC
DAC is the abbreviation for Dedicated Admin Connection.DAC is a special diagnostics connection which is available to trouble shoot when SQL Server is not responding to normal queries. And as the name implies this can be used only by members of sysadmin fixed server role.
How it Works?
By default SQL Server listens on TCP port 1433 which is the default port of SQL, so when a user executes any queries, it uses server port 1433 for executing
the same and produces result to client. DAC listens by default on 1434 so it is not muddled with user traffic; hence it is always available for connections.
DAC consumes minimal resources and as it is meant only for diagnostics there are limitations on what can be done inside a DAC connection. Below mentioned
are the few limitations:
1) There can be only one DAC connection allowed per server. If you try to open 1 more DAC connection you receive a error 17810 and the connection is denied.
2) Command like BACKUP, RESTORE cannot be run
3) As only limited resources are available do not run complex queries which have multiple joins.
4) By default ,DAC cannot be accessed from remote machines, which can be changed by tweaking server configurations using
Sp_configure ‘remote admin connections’,1
Reconfigure with Override
Now let’s see what all works effectively in a DAC connection.
Querying dynamic management views
Sys.dm_exec_requests - To find blocking queries.
Sys.dm_os_memory_cache_counters - To check for health of system memory cache.
Sys.dm_exec_sessions - For information about active sessions.
Querying Catalog views like sys.objects, sys.columns, etc…
DBCC Commands
DBCC FREEPROCCACHE – To remove all procedures from procedure cache
DBCC FREESYSTEMCACHE – To remove all unused entries from system cache
DBCC SQLPERF(LOGSPACE) – To find log file usage
KILL command to kill any connections
How to Use
From command line
SQLCMD -S -E -A
Here -A is the paramter which specifies it is a DAC connecion.
From SQL Manangement Studio
No comments:
Post a Comment