2

I need to be able to grab deadlocks from the SQL Server log file (for various reasons, cannot use Extended events (and that includes the default system health extended event) or WMI)

I have enabled the 1222 trace flag and get the deadlocks in the file.

However, SQL Server is stripping out all of the XML chevrons, which makes parsing the trace flag much more difficult and error-prone!

Does anyone know if it's possible to get SQL Server to log the actual XML into the log file so I can load it into an XML parser?

Improve this question
2
  • 2
    Why can't you use Extended Events or WMI? (BTW: I have always found it strange too that the documentation and many articles always claim this is an XML format when it is clearly not)
    – Martin Smith
    Commented Mar 4, 2017 at 20:52
  • Yes, it is odd to call it XML when it's not, although it clearly was once! I'd love to know why they saw fit to remove XMLness from it - I wonder if it's a "security" concern, to ensure there's never anything approaching XHTML in the logs which could accidentally be dumped raw in a browser and become susceptible to a phishing/hijack type attack... just can't think of any other reason!!!!
    – Mark
    Commented Mar 6, 2017 at 14:48

1 Answer 1

Reset to default
1

Trace flag 1222 will

Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

For the limitations (cannot use - Extended events (and that includes the default system health extended event) or WMI event notifications (script that I wrote to log deadlock info to a table)) that is the max you can get.

Remember that system_health session is running by default.

Improve this answer
3
  • 1
    Thanks for the reply - however, by removing the chevrons, the contents of the log file are no longer XML at all, XSD compliant or otherwise (i.e. it would be possible to load the data into an XML parser if it was XML!)
    – Mark
    Commented Mar 3, 2017 at 17:41
  • @Mark TF 1222 defination says it
    – Kin Shah
    Commented Mar 4, 2017 at 19:09
  • It wouldn't be the first time a Microsoft Doc was wrong... I think it's reasonable for XML to be loadable into an (any) XML parser, no? It's clearly not XML any more when it's stripped of its chevrons... and adding them back is remarkably hard and, ironically, requires a lot of knowledge about the structure of the XSD-less "XML"
    – Mark
    Commented Mar 6, 2017 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.