You are the database administrator of your company. The network contains a SQL Server 2008 computer thathas 15 databases. Users report that some queries take a long time to complete. You investigate and discoverdeadlocks are causing this problem.
You want to receive information about the resources and types of locks that are causing deadlocks along with thecurrent command affected by the deadlocks.
You want to receive this information in Extensible Markup Language(XML) format.
Which Transact-SQL statement should you run?

A.
DBCC TRACEON (1204);

B.
DBCC TRACEON (1204, -1);

C.
DBCC TRACEON (1222);

D.
DBCC TRACEON (1222, -1);

Explanation:

You should run the following Transact-SQL statement: DBCC TRACEON (1222, -1);
A deadlock is a condition in which two or more tasks are blocked permanently by each other when each task hasa lock on a resource that the other task is trying to lock. When a deadlock occurs, the database engine decideswhich task participating in the deadlock should be ended. This decision is made based on the transaction that isthe least expensive to roll back. Alternatively, you can use the SET DEADLOCK_PRIORITY statement to definethe priority of sessions participating in a deadlock. You can set deadlock priority to
LOW , NORMAL , or HIGH , or itcan also be set to a value in the range from -10 to 10. When you manually set deadlock priority, the session withlower priority is terminated by the database engine. When deadlocks occur, trace flags 1204 and 1222 provideinformation about the deadlocks, which is recorded in the SQL Server 2008 error log. Trace flag 1204 returnsinformation about resources and types of locks that are causing deadlocks. Trace flag 1222 also provides thesame information in XML format that does not comply with the XML Schema Definition (XSD) schema. Trace flag1204 and 1222 are global trace flags that can be enabled either by using the DBCC TRACEON statement or byusing the sqlservr.exe -T
command. The complete syntax for using the DBCC TRACEON statement is as follows: DBCC TRACEON ( trace# [ ,…n ][ , -1 ] ) [ WITH NO_INFOMSGS ];
The trace# parameter specifies the number of the trace flag that you want to enabled. The n parameter indicatesthat multiple trace flags can be specified in a single command. The
-1 parameter is used to globally turn on traceflags. The WITH NO_INFOMSGS parameter can be used to suppress all informational messages. You should not run the following Transact-SQL statement: DBCC TRACEON (1204); Trace flag 1204 provides the required information stated in the scenario, but it does not provide the information inXML format as required. Also, because trace flag 1204 is a global trace flag, you must specify the -1 parameter inthe statement. You should not run the following Transact-SQL statement: DBCC TRACEON (1204, -1); Trace flag 1204 provides the required information stated in the scenario, but it does not provide the information inXML format as required. You should not run the following Transact-SQL statement: DBCC TRACEON (1222); Trace flag 1222 is a global trace flag. Therefore, you must specify the -1 parameter in the statement.

Objective:
Monitoring and Troubleshooting SQL Server

Sub-Objective:
Identify concurrency problems.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > Trace Flags (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData (Database Engine) > Accessing and Changing Database Data > Locking and Row Versioning > Locking inthe Database Engine > Deadlocking > Detecting and Ending Deadlocks