Monday, February 8, 2010

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 -


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.

No comments: