Dedicated Admin Connection feature for SQL 2005/2008

DBAs would run into situations where SQL server becomes unresponsive to the point that it doesn't allow new connections. Since we cannot login , we won't be able to figure out the rouge query (or queries :-)) that caused the problem.

SQL 2008 & 2005 allow you to connect to the sql server even under these circumstances using a Dedicated Admin Connection (or DAC).

To use it, you would need to first enbale it using below statements -

sp_configure 'REMOTE ADMIN CONNECTIONS', 1;
GO
RECONFIGURE;
GO

Then connect to the server using SSMS new query Or SqlCmd. You will have to use 'ADMIN:' before the server name. For example if your server name is xyz,
then for DAC connection the server name would be ADMIN:xyz

DAC connection doesn't work with SSMS object explorer and only 1 DAC connection is allowed at a time.

DMV Query For Current Executing SQL Statement

Often times for troublshooting a running big batch of query (for example a migration script) you would need to know the current sql statement executing. You can use below query after identifying the session id (spid) for the batch :

SELECT SUBSTRING(t.[text],
1+(r.statement_start_offset/2),
1+(
(
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset
)/2
)
)
FROM
sys.dm_exec_sessions s LEFT JOIN
sys.dm_exec_requests r ON (r.session_id = s.session_id) CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = ? /* Session id that you need to investigate */