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
- Open SQL Server Management Studio (SSMS)
- Click on the Database Engine Query Icon to open the login prompt or from SSMS go to File>>New>>Database Engine Query
- Enter ADMIN:Server Name, if using an instance use ADMIN:Server Name\Instance Name
- Enter the sysadmin credentials
If you get the following error, re-login using the Database Engine Query login prompt
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)'