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
Can you explain how long are locks retained within the repeatable_read and serializable isolation levels, during a read operation with row-level locking?
You have to store user responses of ‘yes’ and ‘no’ what kind of data type is best suited for this task?
Can I work with several databases simultaneously? : sql server management studio
How do I find my localdb version?
How to create an multi-statement table-valued function?
What is the purpose of data source?
How to sort the query output with order by clauses in ms sql server?
Explain following error properties?
How we create SQL Server 2005 Reporting Services ? Give me Sample
What are data driven subscriptions?
which table keeps the locking information? : Sql server administration
what is the system function to get the current user's user id? : Sql server database administration
Explain partitioned view?
How do you send email on SQL Server?
How to connect to SQL Azure Database by using sqlcmd?
