You have a database that is experiencing deadlock issues when users ru
n queries.
You need to ensure that all deadlocks are recorded in XML format.
What should you do?
A. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
B. Enable trace flag 1224 by using the Database Consistency Check
er(DBCC).
C. Enable trace flag 1222 in the startup options for Microsoft SQL Server.
D. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.
Explanation:
When deadlocks occur, trace flag 1204 and trace flag 1222 return information
that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.
The output format for Trace
Flag 1222 only returns information in an XML-like format.
References: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
C
7
0
C is correct thought we need a service restart to apply
the best approach is to execute
DBCC TRACEON(1222,-1) and also add to startup parameters
1
0