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?
Best Answer
Trace flag 1222 will
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.