Home Article SQL Server DAC Restrictions & Troubleshooting 
DAC Restrictions & Troubleshooting PDF Print E-mail
User Rating: / 0
PoorBest 
SQL Server - Articles
Written by S. Singh   

As we all know DAC exists solely for diagnosing server problems in rare circumstances, there are some restrictions:

  • Only one DAC is allowed per instance of SQL Server. If a DAC connection is already active, any new request to connect through the DAC is denied with error 17810.
  • By Default SQL Server Express does not listen on the DAC port unless started with a trace flag 7806.
  • The DAC initially attempts to connect to the default database associated with the login. After it is successfully connected, you can connect to the master database. If the default database is offline or otherwise not available, the connection will return error 4060. However, it will succeed if you override the default database to connect to the master database instead using the following command:
    sqlcmd –A –d master
  • SQL Server prohibits running parallel queries or commands with the DAC. For example: RESTORE / BACKUP, error 3637 is generated if you execute either of the mentioned statements with the DAC
  • Only limited resources are guaranteed to be available with the DAC. Do not use the DAC to run resource-intensive queries (for example. a complex join on large table) or queries that may block. You might be able to terminate the DAC session using CNTRL-C but it is not guaranteed. In that case, your only option may be to restart SQL Server.
  • To guarantee connectivity and troubleshooting with the DAC, SQL Server reserves limited resources to process commands run on the DAC. These resources are typically only enough for simple diagnostic and troubleshooting functions, such as those listed below.

Although you can theoretically run any Transact-SQL statement that does not have to execute in parallel on the DAC, Microsoft strongly recommend that you restrict usage to the following diagnostic and troubleshooting commands:

  • 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. 
  • 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.
  • 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.

For More Information Refer: MSDN

 

APPLIES TO
MS SQL Server 2005
MS SQL Server 2008

 

Regards,

Sarabpreet Singh

Comments
RSS
Vikas Rana   |IP of unregistered User:121.241.115.2 |2010-05-21 07:23:53
Good Information
Only registered users can write comments!

3.26 Copyright (C) 2008 Compojoom.com / Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."

 

S. Singh

 

Sarabpreet’s Seven years of enriched experience and intellectual skills, have provided him an edge over the others by way of endowing him an opportunity to work and grow along with industry leaders. The keen urge of continuously learning and growing to evolve himself and being adaptable to changing environment has proved to be a milestone for his carrier by way of doing certification for database administrator from the I.T Industry leader Microsoft. His core competency lies in administration of SQL Server 2000, 2005 & 2008.
Presently Sarabpreet is working as a Freelancer and provides support to Multiple Clients Handles more than 90 servers & 800+ Databases, all responsibilities related to same such as Designing High Availability Database Solutions Clustering, Log-shipping and Mirroring, Backup\ Restore (Disaster Management), Automating SQL Server monitoring and improving current processes, Identifying performance bottlenecks and providing improvement recommendations, Installation and configuration of SQL 2000 and 2005 cluster, Configuring and troubleshooting backup/DR jobs, Implementing database change requests received from developers, Resolving performance issues, performance tuning, Database Administration and resolving day-to-day issues.

Sarabpreet’s Seven years of enriched experience and intellectual skills, have provided him an edge over the others by way of endowing him an opportunity to work and grow along with industry leaders. The keen urge of continuously learning and growing to evolve himself and being adaptable to changing environment has proved to be a milestone for his carrier by way of doing certification for database administrator from the I.T Industry leader Microsoft. His core competency lies in administration of SQL Server 2000, 2005 & 2008. 

 

                                            Presently Sarabpreet is working as a Freelancer and provides support to Multiple Clients Handles more than 90 servers & 800+ Databases, all responsibilities related to same such as Designing High Availability Database Solutions Clustering, Log-shipping and Mirroring, Backup\ Restore (Disaster Management), Automating SQL Server monitoring and improving current processes, Identifying performance bottlenecks and providing improvement recommendations, Installation and configuration of SQL 2000 and 2005 cluster, Configuring and troubleshooting backup/DR jobs, Implementing database change requests received from developers, Resolving performance issues, performance tuning, Database Administration and resolving day-to-day issues.


Statistics

Content View Hits : 63190

Powered by Solutions-Guru.com!. Designed by: budget hosting reseller hosting packages Valid XHTML and CSS.