If all goes “nuts and bolts” with your SQL Server – what do you do?
Well, hopefully you have heard about the DAC before. If not, you hear about it now. DAC is the Dedicated admin connection for SQL Server. As an admin you should make sure that the DAC is enabled, tested and that you have a set of scripts which will give you quick information about your system, once you are connected.
DAC is like a back door. If any other connections to your server are refused for one reason or another, you will still be able to connect through the DAC and run some queries to determine the problem, and eventually kill a process etc…
So, how do we enable DAC?
In SQL 2005 you can go to the “Surface Area Configuration”(SAC) and then SAC for Features. From there you can enable the DAC.
In SQL 2008 you can right-click the instance in SSMS and go to Facets, select the Surface Area Configuration and change the “RemoteDacEnabled” value to True.
Or, you could run the following script:
Use master
GO
sp_configure 'show advanced options' , 1
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO
And then, you can connect to SQL Server by typing the ADMIN: word before the server/instance name (for example: ADMIN:MySQLServerName or ADMIN:MySQLServerName\MyInstance).
If you keep getting the following error “Dedicated administrator connections are not supported.”, then you should keep in mind that you can connect to the DAC ONLY through a Query Window, and NOT through a Database Engine Query (when it comes to using SSMS for the DAC). Otherwise you can connect through a command line, of course. 
When it comes to a small set of queries you should have ready…
-- Locking Information
SELECT * FROM sys.dm_tran_locks
GO
-- Cache Status
SELECT * FROM sys.dm_os_memory_cache_counters
GO
-- Active Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO
GO
sp_configure ‘show advanced options’ , 1
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE
GO
