Similar to safe mode in windows we have Dedicated Administrator Connection (DAC) in SQL Server. SQL Server 2008 keeps a dedicated scheduler available at all times for the DAC. Therefore, you should (almost) always be able to connect to SQL Server by using the DAC. You can log into the SQL Server instance with a DAC while other users are logged in at the same time. However, only one DAC session is allowed at any one time.
By default the DAC is only enabled for accounts logged onto the local machine. For production servers, that means it only works for remote desktop sessions to non-clustered SQL Server instances. If your instance is clustered or if you’re connecting over TCP/IP, you’re out of luck unless you change one setting. That setting is ‘Remote Admin Connections’.
sp_configure 'remote admin connections', 1; GO RECONFIGURE; GO
How to Connect to the DAC
You can connect to the DAC using the command line. Use the “-A” option with SQLCMD.exe.
sqlcmd -A -d master -E -S instance_name
We can also connect in Management Studio itself by prefixing the instance name with “Admin:”. Object Explorer cannot connect using the DAC.
To connect to DAC using Database Engine Query, in SQL Server Management Studio click:
File -> New -> Database Engine Query
- SQL Server Browser service must be running to connect to Dedicated administrator connection (DAC).
- It is possible to make only one connection at a time with DAC.
- If the DAC is already in use, the connection will fail.
From the DAC you can perform the following actions:
- Run T-SQL scripts
- Run DBCC utility commands
- Access Dynamic Management Views (DMVs)
- Access catalog views
SQL DBA Blog 