Answer Posted / 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 View All Answers
What are the advantages to use stored procedures?
Can you explain different types of joins?
1.how to find the dead lock in sql server? 2.How to fine the memory leaks in sql server? 3.suppose transaction log file increasing what action will take ?
Can an entity have two primary keys?
What are audit control procedures?
Explain system functions or built-in functions? What are different types of system functions?
What are the different types of data sources in ssrs?
Define cross join in sql server joins?
How to create hyperlink from returned sql query ?
how to trace the traffic hitting a sql server? : Sql server database administration
What is page-level compression?
What is TDS(Tabular Data Stream) Gateway?
How to check parameter value in stored procedure sql server?
What is the exact numeric data type in sql?
What is advantage data architect?
