Developing SQL Databases — Question 78

You have a database that is experiencing deadlock issues when users run queries.
You need to ensure that all deadlocks are recorded in XML format.
What should you do?

Answer options

• A. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
• B. Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).
• C. Enable trace flag 1222 in the startup options for Microsoft SQL Server.
• D. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.

Correct answer: C

Explanation

The correct answer is C because enabling trace flag 1222 captures deadlock information in XML format, which is essential for analyzing deadlocks. Option A is incorrect because sys.dm_tran_locks does not log deadlocks in XML. Option B is not relevant since trace flag 1224 does not provide XML formatted deadlock information. Option D can monitor deadlocks but does not specifically record them in XML format.