DAC – Dedicated Administrator Connection
Use Command below to connect as DAC
| SQLCMD -A |
It is on by Default for local server.
For remote(via network) enable it by running query below
| sp_configure 'remote admin connections' |
For Clusters, recommended to enable remote admin connections as no local DAC available
| ---- Query to Find if DAC - Dedicated Admin Connection used ----- select s1.session_id, s1.program_name,s1.host_name,t1.name from sys.tcp_endpoints t1 join sys.dm_exec_sessions s1 on t1.endpoint_id = s1.endpoint_id where t1.name = 'Dedicated Admin Connection' --- End of the Query ----- |
| --- Query to check the DAC ---- --- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) --- select * from sys.dm_os_schedulers |
Usage:
when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection
- Use DAC sparingly for monitoring and Troubleshooting
- Connect to master database first
- Use lightweight queries.
- Not advised for long running queries like ( DBCC CHECKDB, DBREINDEX, INDEXDEFRAG )
No comments:
Post a Comment