You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Manufacturing that stores data from the Manufacturing department. One day Domain.com users complain that they cannot access the CK_Manufacturing database on Certkiller -DB01. When you attempt to open the SQL Server management Studio, you discover that Certkiller -DB01 is not responding. You are unable to connect using sqlcmd with a standard connection. You need to be able to connect to Certkiller -DB01 to be able to diagnose the problem.
What should you do?
A.
Run sqlcmd with the -A option from the command prompt.
B.
Run the sp_configure ‘remote admin connections’, 1; statement.
C.
Run the DBCC CHECKDB statement.
D.
Determine the session ID of the errant process and terminate that process.
Explanation:
The sqlcmd command with the -A option to establish a dedicated administration connection (DAC) to Certkiller -DB01. You will be able to run queries to diagnose the problem once a DAC is established.
Incorrect Answers:
B: By default, only local dedicated administration connections (DACs) are allowed as DAC only listens on the loop-back IP address (127.0.0.1), port 1434. The sp_configure ‘remote admin connections’, 1; statement allows a remote DAC to be established. It does not establish the DAC.
C: You will not be able to run the DBCC CHECKDB statement on a nonresponsive server until you establish a dedicated administration connection (DAC). Furthermore, the DBCC CHECKDB statement is used to check the structural and logical integrity of a database. It is not used to diagnose a nonresponsive server.
D: You will not be able to identify the session ID of any process on a nonresponsive server until you establish a dedicated administration connection (DAC).
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: sqlcmd commands Microsoft SQL Server 2005 Books Online (2006), Index: remote admin connections option
Microsoft SQL Server 2005 Books Online (2006), Index: DBCC CHECKDB statement