THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Dedicated Admin Connection

Dedicated Admin Connection (DAC) is one of the highly touted features introduced in SQL Server 2005. Now that it has been around for a few years, I wonder how it is actually being used or whether it's being used at all in the real world.

I have not run into any real production scenarios where I'm forced to use DAC. But that may just be due to my own narrow exposure to SQL Server problems. If you have used it to solve real problems (not just for testing or folling around), I'd very much like to know what kind of problems they were.

Published Tuesday, May 20, 2008 1:31 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

I have not had a case where I've really had to use it yet, but I am glad that it is there (just in case).

May 20, 2008 1:05 PM
 

Adam Machanic said:

I have used it once.  The server in question was set for MAXDOP 0, and a huge, complex query was using all of the processor time.  The DAC let me get in and kill it (and change MAXDOP shortly thereafter!)

May 20, 2008 1:18 PM
 

DonRWatters said:

I've never had a chance to use it.  A blessing and a curse, really.

May 20, 2008 2:34 PM
 

Neeraj said:

I really like this new feature and used it couple of times for testing and once for big real issue...

We have logon trigger enabled on all our prod sql servers...One of my colleague altered the logon trigger script(some testing) and executed on one of prod server that really hung everything..Users were not able to connect to server and no one from DBA group was able to connect to server through SSMS .. always throwing some weird error related to triggers..like "trigger enabled connection not allowed".Googled the error didn't find anything..We saw thousands of error messages in Errorlog in 10 minutes.. then i used DAC to    connect to the server and Disabled the logon triggers on server and that allowed us to connect to server.

May 20, 2008 8:43 PM
 

Praveenbarath said:

Its good with some extents, Whats DAC enhancement in SQL 2008

May 21, 2008 12:43 PM
 

John Paul Cook said:

I used DAC to regain control over a server that was unresponsive.

May 21, 2008 5:03 PM
 

Ranga said:

To practice and get familirized with DAC process (of course in dev/testing) is there any we may simulate a complete server lockdown ?

May 22, 2008 9:49 AM
 

AaronBertrand said:

First thought that comes to mind... set all databases to single user mode?  Or you could start the entire service that way (like when you need to rebuild master)?

May 22, 2008 12:04 PM
 

Linchi Shea said:

But that (i.e. putting SQL Server in the single user mode) is really not the same as SQL Server beign unresponsive. Keeping all the workers locked is probably a better simulation.

May 24, 2008 1:59 PM
 

SMaloney said:

Very recently used DAC to connect to an almost completly unresponsive production DBMS (and to help determine the probable cause which turned out to be related to an IO subsystem failure). Quite some while prior to that a DAC has also been somewhat helpful on another occasion in establishing a connection to a memory bound DBMS (sufficient to run various DMV queries to make that assessment at any rate). HTH

May 27, 2008 10:51 PM
 

TiborKaraszi said:

"To practice and get familirized with DAC process (of course in dev/testing) is there any we may simulate a complete server lockdown ?"

Thinking out loud:

How about kicking off some weird EXE using xp_cmdshell? Or even start notepad.exe? Do that from enough connections and you should have eaten all "normal" schedulers leaving DAC the only available scheduler...

May 28, 2008 12:13 PM
 

brejk said:

Last year I used it once to disable some logon trigger working inproperly (it caused that nobody could log on to the instance - but it was only a test environment not a production one).

May 30, 2008 1:42 AM
 

Mani Singh said:

Is there any other option than DAC to logon to SQL Server, like command line options(sqlcmd or osql), etc. and what is the behaviour vs DAC. DAC is the Admin connection (of course), but how about command line options using SA. I would like to know the QUICK TIP and cons of sqlcmd over DAC(if any)

June 6, 2008 10:21 AM
 

Kalen Delaney said:

Mani

>> I would like to know the QUICK TIP and cons of sqlcmd over DAC <<

sqlcmd and DAC are not mutually exclusive. sqlcmd is a tool, DAC is a dedicated administrator connection. You can use the DAC connection either through the Management Studio or through sqlcmd. You cannot use osql to use DAC.

Using the DAC is not like using a normal connection with SA. DAC allows you to connect even if SQL Server is hung.

~Kalen

June 7, 2008 3:40 PM
 

Thomas Pullen said:

One cool thing with the DAC is that you can see more deeply inside the resource database using it.

On my servers, I always enable it at install time, but I've never used it in anger (touch wood, fingers crossed).

June 19, 2008 6:30 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement