How to access a running instance of SQL Server Database Engine to troubleshoot MSSQL from the command line

  • Microsoft provides the Dedicated Administrator Connection or DAC to fix problems when the SQL Server becomes unresponsive [Microsoft's euphemism for hanged]. The Dedicated Administrator Connection allows the administrator or anybody belonging to the sysadmin group to log in even when nobody else can.
  • The Dedicated Administrator Connection allows the administrator or anybody belonging to the sysadmin group to close other people connections... and to resolve the issues including restarting the SQL Server.
  • By default Microsoft SQL Server only allow the Dedicated Administrator Connection or DAC from the SQL Server console.
  • Often because of the SQL Server is on the ropes, we don't want to use the Management Studio. I always try use the command line.
  • There has been times when I wasn't able to load the Management Studio, so I use the command line.
  • I most often use sqlcmd command line when close blocking sessions.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Connecting with the Dedicated Administrator Connection from the command line

  1. C:\>sqlcmd -S sqlserver -A
  2. 1> SELECT session_id, blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
  3. 2> GO
session_id blocking_session_id
---------- -------------------
71         79

(1 rows affected)
  • Line 1: -S sqlserver: sqlserver is the name of the server.
  • Line 1: -A: use the Dedicated Administrator Connection or DAC.
  • There is only 1 Dedicated Administrator Connection allowed.
  • If the Dedicated Administrator Connection is already used, it will fail.