Developing Microsoft SQL Server Databases — Question 34

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:
✑ Provide a representation of the deadlock in XML format.
✑ Minimize the impact on the server.
What should you create?

Answer options

• A. A SQL Server Profiler trace
• B. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
• C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
• D. A script that enables trace flags

Correct answer: A

Explanation

The correct answer is A, as SQL Server Profiler can capture deadlock information in XML format with minimal performance overhead. Options B and C involve using dynamic management views that do not directly log deadlock information in the required format, and option D, while it may help with debugging, does not specifically log deadlocks or provide an XML representation.