![]() ![]() ![]() The first requirement is to turn on the appropriate Trace Flags. We do have to do two things in order to make the scripts I have written work properly. Since capturing Deadlock info is not turned on by default. It would be the dutiful DBA's job to log into the server in question and dig into the Error Log to get the Deadlock details. But getting the generic alert that SQL Server can create simply will tell you "something" has occurred. I not only wanted to be told when the Deadlock occurred, I wanted to also be emailed the actual Deadlock information.Ä®very time a deadlock occurs in SQL Server, the detailed info about things like the SPID, the statement that was running, who the victim was, and so on does get logged. While it was a fairly straight forward process on how we get notified a deadlock has occurred, I wanted a bit more. In addition other actions such as the an_handle have similar changes that require changing the code to process the Event XML to capture the values being output.I had spent many hours on Google trying to find the best way to send a notification on deadlock occurrences. This same thing applies to all of the XML data elements including the sqlserver.tsql_stack and sqlserver.tsql_frame Actions. query() XPATH for it as shown in the following code example:Ä®vent_data.query('(event/data/value/deadlock)') AS DeadlockGraph query() operation on the Event XML specifying the deadlock node as a part of the. value() XML function along with a CAST() operation to using a. To read the XML Document, you have to switch from using the. This has a significant impact to how you actually access the XML data in Denali CTP3. In SQL Server 20R2, the value element is XML escaped entirely as text, but in SQL Server Denali CTP3, the value attribute contains a valid XML document as a child node in the XML itself. ![]() If you compare the two bold sections to each other you will notice the difference. Instead in SQL Server Denali CTP3, the event output is as follows: This unfortunately has broken a number of my scripts that were initially written for SQL Server 20R2 that expect the XML output as text in the value element as follows: If you run this same code in SQL Server Denali CTP3, the output will not be the xml_deadlock_report but instead the textual data that was included in the sub-nodes of the value node for the xml_deadlock_report events in the target. SELECT CAST(target_data AS XML) AS TargetDataĬROSS APPLY TargetData.nodes AS XEventData (XEvent) In SQL Server 20R2, the query to retrieve the deadlock graph from the system_health session was (excluding the work around that was included in the original article since the xml_deadlock_report was fixed in a later Cumulative Update and the latest Service Pack for SQL Server 20 R2).ĬAST(event_data.value('(data/value)', 'varchar(max)')) AS XML) AS DeadlockGraph A good example of this is the xml_deadlock_report output, which I previously discussed in my SQL Server Central article, In SQL Server 20R2, the Event XML represented the output of XML data elements differently than in SQL Server Denali CTP3. ![]() While working on validating my demos for the 24 Hours of PASS and my PASS Summit 2011 Precon â Extended Events Deep Dive, I noticed a significant, and breaking change to the Event XML output for the raw event data in the ring_buffer and file_target in SQL Server Denali. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |