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)'

 

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s