What is DAC? what is the use of it?

Question Posted / vamsimadineni

1 Answers
5403 Views
Wipro, I also Faced
E-Mail Answers

What is DAC? what is the use of it?..

Answer / samba shiva reddy . m

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.

This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.
Uses:
Querying dynamic management views for basic diagnostics such as sys.dm_tran_locks for the locking status, sys.dm_os_memory_cache_counters to check the health of caches, and sys.dm_exec_requests and sys.dm_exec_sessions for active sessions and requests. Avoid dynamic management views that are resource intensive (for example, sys.dm_tran_version_store scans the full version store and can cause extensive I/O) or that use complex joins. For information about performance implications, see the documentation for the specific dynamic management view.
Querying catalog views.
Basic DBCC commands such as DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, and DBCC SQLPERF. Do not run resource-intensive commands such as DBCC CHECKDB, DBCC DBREINDEX, or DBCC SHRINKDATABASE.
Transact-SQL KILL <spid> command. Depending on the state of SQL Server, the KILL command might not always succeed; then the only option may be to restart SQL Server. The following are some general guidelines:
Verify that the SPID was actually killed by querying SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>. If it returns no rows, it means the session was killed.
If the session is still there, verify whether there are tasks assigned to this session by running the query SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>. If you see the task there, most likely your session is currently being killed. Note that this may take considerable amount of time and may not succeed at all.
If there are no tasks in the sys.dm_os_tasks associated with this session, but the session remains in sys.dm_exec_sessions after executing the KILL command, it means that you do not have a worker available. Select one of the currently running tasks (a task listed in the sys.dm_os_tasks view with a sessions_id <> NULL), and kill the session associated with it to free up the worker. Note that it may not be enough to kill a single session: you may have to kill multiple ones.

SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.
Connecting with DAC :
By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

Only members of the SQL Server sysadmin role can connect using the DAC.

The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A).
You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<instance_name>. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<instance_name>.

Is This Answer Correct ?

3 Yes

0 No

Post New Answer