SQL Server DAC (Dedicated Administrator Connection)

SQL Server DAC (Dedicated Administrator Connection)

Have you ever had a query go rogue? CPU at pegged at 100%? This will help!

Note: This is only for SQL Server 2005 and later…

There is some groundwork to do.

Setting up the DAC

Run these statements as an administrator:

sp_configure 'remote admin connections',1
GO
RECONFIGURE
GO

This will enable the ability to connect to the server.

Using DAC When IT Matters!

You can do two things use sqlcmd to connect to the SQL Server or a query window (not the Object Browser). The trick is before the name of the server prefix the name of the server with admin:.

Example:

admin:servername

A note of interest, you are limited by what you can do in this mode. Nothing that needs to store temporary tables and things like that. You can even use this if you do something terrible like corrupt the master or tempdb databases.

I hope you never need this!

Advertisements

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s