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?
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.
Suggested Answer:C🗳️
When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin 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
The question asks : "You need to ensure that all deadlocks are recorded in XML for"
Flag 1222 does not return xml. You have to use Sql Server Profiler.
Okay Very confused between 1222 and Profiler.
1222 - Is XML like format
and Profiler - does Lock:Deadlock event provides XML output or it should be Lock: Deadlock Graph event.
The Deadlock Graph event class provides an XML description of a deadlock .
The Deadlock Graph event class : Text data -XML description of the deadlock.
Lock:Deadlock Event Class : Text data - Text value dependent on the lock type that was being acquired.
So I think the event name provided in the option for the profiler doesn't not provide the XML . So the answer should be 1222 . Please correct me if I am wrong .
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-deadlock-event-class?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/deadlock-graph-event-class?view=sql-server-ver15
This is absolutely correct: lock:Deadlock = text data. Deadlock graph = xml. answer specifies lock:deadlock so it can't be correct. Correct answer is to enable 1222
There is no "correct" answer. There are 2 strong contenders.
Trace Flag 1222 provides the output in an XML "like" syntax, but it's not XML.
SQL Profiler can produce XML from the Deadlock Graph event, but not the Deadlock event.
So either the question is wrong and it meant to say "XML like", or answer D is a typo and should have been "Deadlock Graph"
D: SQL Server Profiler is correct:
SQL Server Profiler has the ability to capture the deadlock related information as XML files which can be analyzed to determine the overall locking and blocking issue. Capturing this additional information can be beneficial if you experience long locking and blocking chains frequently.
https://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/
1222 not exactly XML:
Flag 1222:- Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. The format has three major sections. The first section declares the deadlock victim. The second section describes each process involved in the deadlock. The third section describes the resources that are synonymous with nodes in trace flag 1204.
Confused now : Same site
https://www.mssqltips.com/sqlservertutorial/252/tracing-a-sql-server-deadlock/
If you want to capture this information in the SQL Server Error Log you need to enable one or both of these trace flags.
1204 - this provides information about the nodes involved in the deadlock
1222 - returns deadlock information in an XML format
Trace Flag 1222:Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. The format has three major sections. The first section declares the deadlock victim. The second section describes each process involved in the deadlock. The third section describes the resources that are synonymous with nodes in trace flag 1204.
If you've looked the output from the log for 1222, it's not in XML format. Rather it's in 3 main sections, as the XML format from Profiler is in 3 main sections. And it's a list of attributes and values, as you would find in XML. But it's not XML
The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim.
Use the Lock:Deadlock event class to monitor when deadlocks occur and which objects are involved. https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-deadlock-event-class?view=sql-server-ver15
capturing deadlocks without a graph by using Trace Flags 1204 and 1222. You can enable these trace flags by using the following syntax: DBCC TRACEON(1204,1222,-1). Whenever a deadlock occurs, the deadlock victim and the other transaction involved in the
deadlock appear in the SQL Server log.
upvoted 5 times
...
This section is not available anymore. Please use the main Exam Page.70-762 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
lukadataowner
Highly Voted 5 years agoitexamsmicrosoft
4 years, 10 months agoCarloscastrelos
4 years, 6 months agomelvin9900
Highly Voted 4 years, 9 months agodatabasejamdown
4 years, 6 months agoHoglet
Most Recent 4 years, 5 months agostm22
4 years, 9 months agomelvin9900
4 years, 9 months agoMML
4 years, 11 months agoHoglet
4 years, 5 months agoNickname17
4 years, 12 months agogtc108
5 years agoNickname17
5 years agoJohnFan
5 years, 2 months ago