SQL Server 2005 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.
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. Remote connections are not permitted unless they are configured by using the Surface area configuration for features and navigating to database engine to enable DAC or the sp_configure stored procedure as shown below,
SP_CONFIGURE 'remote admin connections',1 GO RECONFIGURE GO
Only members of the SQL Server sysadmin role can connect using the DAC.
There are 2 options by which we can connect using DAC
1. Using SSMS
2. Using Sqlcmd
To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name. Object Explorer cannot connect using the DAC.
In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query. The Connect to Database Engine dialog box opens.
In the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named DEEPAKTEST, type ADMIN:DEEPAKTEST.
Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.The connection is made.If a DAC already exits, the connection will fail with an error indicating it cannot connect.
Using Sqlcmd we can connect to sql server as a dedicated administrator as follows,
Go to command prompt and type as shown below to connect using windows authentication.
Sqlcmd -S DeepakTest -E -A
or type as shown below
Sqlcmd -S Admin:DeepakTest
Go to command prompt and type as shown below to connect using Sql server authentication.
Sqlcmd -S DeepakTest -U sa -P XXXX -A
where XXXX is the password for the login sa
DAC Port :
SQL Server listens for the DAC on a dedicated TCP/IP port. The error log contains the port number the DAC is listening on. By default the DAC listener accepts connection on only the local port.
Once the remote administration connection is configured, the DAC listener is enabled without requiring a restart of SQL Server and a client can now connect to the DAC remotely. You can enable the DAC listener to accept connections remotely even if SQL Server is unresponsive by first connecting to SQL Server using the DAC locally, and then executing the sp_configure command to accept connection from remote connections.
The DAC port is assigned dynamically by SQL Server during startup. If SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number as shown below,
sqlcmd Stcp:servername, port number
The SQL Server error log lists the port number for the DAC, which is 1434 by default. If SQL Server is configured to accept local DAC connections only, connect using the loopback adapter using the following command:
Because the DAC exists solely for diagnosing server problems in rare circumstances, there are some limitations on the connection:
1. To guarantee that there are resources available for the connection, 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.
2. To conserve resources, SQL Server 2005 Express Edition does not listen on the DAC port unless started with a trace flag 7806.
3. 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.
4. SQL Server prohibits running parallel queries or commands with the DAC. For example, error 3637 is generated if you execute either of the following statements with the DAC:
RESTORE and BACKUP
5. 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. This helps prevent the DAC from compounding any existing server problems. 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.
6. 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.
7. Also avoid 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.
8. Restrict the usage of Transact-SQL KILL 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 = . 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.