Detect the cause of failure using DAC- SQL Server

Failures and problems are the common situation in everyone’s life. But, we should have the ability to overcome or diagnosis the root cause and solve it. Do not get confuse, we are just talking about SQL Server only.

SQL Server may go to an unresponsive state anytime due to various reasons. For instance, a heavily loaded SQL Server instance handling thousands of users many some time block resource and lead server to sleeping state.

In this situation, administrator need special privilege to connect to server to find out the cause for the sudden change in the system. I repeat, unresponsive SQL Server instance is an unusual situation. So, do not get panic. A cool mind can solve thousands of problem then a panic mind.

SQL Server provides a dedicated session to detect and fix the problems in the database server to make it to back to the work.

It is always possible. But, in extreme rare case, it cannot be possible to connect to the server itself.

DAC – Dedicated Administrator Connection

It is a special connection that allows Administrator to connect with SQL instance from the local machine. But, if you would have configured remote admin connections option, then DAC allows you to connect to the SQL instance from the remote machine otherwise restart server.

This connection is used to execute diagnostic queries and troubleshoot problems in the SQL Server instance when you SQL Server is not responding through the normal connection.

Two ways to connect to SQL Server using DAC

1. Command line – SQLCMD

Image shows the connection to default SQL instance using DAC

2. SQL Server Management Studio (SSMS)

Open SSMS and connect to server by creating new query

Note 1: only one DAC connection is allowed per instance. Otherwise, an error will be thrown by the server.

Note 2: only sysadmin users can connect to Server using DAC.

Purpose

1. DAC is not designed to execute parallel queries. So, do not try backup, restore, queries against the DAC rules such as join, blocking.

2. SQL Server Express must be started with a trace flag 7806 to use DAC.

3. Default database must be master in the situation where the user database is corrupted. Master database gives guarantee connection.

4. you are allowed to used DMVs, DBCC queries and function that helps to identify the problems and fix. In case, it helps to kill the blocking sessions in the server.