DAC

What To Do When SQL Server Stops Responding, Did You Try Using the Dedicated Administrator Connection?

Over the years I have seen my share of SQL Servers that have stopped responding. Most of the time in these situations there is a process that has consumed all the available resources on the server. You will need to use the Dedicated Administrator Connection (DAC) to gain access to the SQL Server to diagnose and collect information on the request that caused the issue and to bring SQL back online. The DAC is a safety line so you don’t have to restart the SQL Service and possibly put the database into recovery for several hours. Since DAC uses minimal resources, you will need to keep your queries simple. I have provided some queries below to view the requests on the SQL Server.

Dedicated Administrator Connection

  1. Open SQL Server Management Studio (SSMS)
  2. Click on the Database Engine Query Icon to open the login prompt or from SSMS go to File>>New>>Database Engine Query
  3. Enter ADMIN:Server Name, if using an instance use ADMIN:Server Name\Instance Name
  4. Enter the sysadmin credentials

DAC

 

If you get the following error, re-login using the Database Engine Query login prompt

BAC_Error

 

Now your connected to DAC what do you run?

When using DAC or when SQL Server is under a lot of resource pressure you need to keep you queries simple. Avoid using large complex queries and resource expensive DMV’s.

1.      View the Requests on SQL Server

Select Session_id, 
       Start_Time, 
       Status, 
       plan_handle, 
       database_id, 
       blocking_session_id, 
       wait_type, 
       cpu_time, 
       reads, 
       logical_reads,
       writes
from sys.dm_exec_requests
where status not in ('background','sleeping')

2. View the Session Information

Note you will need the Session ID from the First query

Select session_id, 
       login_name, 
       host_name, 
       program_name
from sys.dm_exec_sessions
where session_id = '[session_id]'

3.      View the SQL Text and Execution Plan.

Note you will need the Plan Handle from the First query

Select text from sys.dm_exec_sql_text([plan_handle);

Select query_plan from sys.dm_exec_query_plan (plan_handle);

4.       View the Database Name

Note you will need the Database ID from the First query

Select name from sys.databases where database_id = '(database_id)'