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!