Sometimes computers fail due to hardware or software. This time your database server is not responding. If it’s SQL Server 2005 you still have an alternative to restarting your production server – Dedicated Administrator Connection. This article will give you an idea on what is DAC, how to use it and when to use it.
What is DAC ?
At a first look, Dedicated Administrator Connection or shorter DAC, is just another connection. And that’s true but this connection is very special. It has its own separate memory area, its own scheduler and even its own port. This way by reserving resources, DAC will remain open for business as long as your instance of Database Engine is still started.
How to use it ?
You can initiate a DAC from SQL Server Management Studio or sqlcmd command-line utility, by prefixing the instance name you will connect to with admin: prefix. In case of sqlcmd you have a second option – the –A switch.
The following examples are equivalent and demonstrate how to connect to a server named MyServer with Integrated Authentication and using DAC.
Example:
sqlcmd –SMyServer -E -A
sqlcmd –Sadmin:MyServer -E
Another thing I want to mention is that only members of sysadmin SQL Server role can use DAC and that by default you can use DAC only locally. For remote connections you have to enable them using the remote admin connections option and sp_configure stored procedure or Surface Area Configuration utility.
Example:
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
You can also use DAC with osql but it’s neither recommended nor supported.
To save resources DAC is disabled in SQL Server Express edition but you can turn it on by using a trace flag. Just add "-T7806” to the startup parameters and you are ready to go.
Maybe you are familiar with this story in which a magical creature (a genie or a goldfish) grants three wishes. The Microsoft SQL Server team grants you just one DAC. So if you use it don’t forget to close it.
Another thing that makes DAC special is the ability to view and even update the system tables. In 99.99% of cases, it’s absolutely unnecessary to modify directly the system tables. For the others there is DAC. To test this, just run the following example on a standard connection and then on DAC:
Example:
SELECT TOP 10 * FROM sys.sysschobjs;
GO
You don’t have to know more about the sys.sysschobjs table other than it is a system table. I will not show you how to modify a system table but I will just mention that you have to start your server in single-user mode.
When to use it ?
As the title says, DAC is a friend in need but not for every need. Do not mistake “administrator” for “administrative”. DAC is not meant for administrative tasks. Having a single thread available some administrative commands like BACKUP and RESTORE, or other parallel queries and commands won’t run in DAC. As a rule of thumb if you can’t connect to your server using a standard connection, then try with DAC and proceed with caution. Remember that you’re using DAC because your server is already in trouble and you have the power to make the situation worse by blocking DAC.
To avoid that; don’t run resource-intensive queries and limit yourself to basic diagnostic ones. I recommend you the new Dynamic Management Views (views that expose the internal state of the server) such as sys.dm_tran_locks to get an idea of the current locks, sys.dm_exec_requests and sys.dm_exec_sessions that will return information about the current request and sessions. Add KILL command to this recipe (to help you terminate misbehaving connections) and you have a pretty good toolset at your disposal.
If you want to avoid blocking DAC you can run your queries with a LOCK_TIMEOUT value set. Another option is to use READ UNCOMMITTED transaction isolation level. Or even better use both.
If there is something that I want you to remember is the fact that you should use DAC only if you need it and do that carefully.
Did I mention that DAC won’t timeout? So don’t forget to close it!
Published in Culminis Compass
Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro