Sql-server – Keeping “SQL Trace was started” and “SQL Trace stopped” from filling up the SQL logs

Here's something I was fiddling with based on some work I had laying around. It doesn't handle the events clustered at a single time well. It could theoretically be helpful anyhow...:)

;WITH ordered_rows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY EventTime) AS Row,
        Identifier, Type, EventTime, DiscoveredDate, FileId FROM #EventDataTemp
)
,filtered_rows AS
(
    SELECT Row, Identifier, Type, EventTime, DiscoveredDate, FileId, 
        CAST(CASE Type WHEN 'Went Off' THEN 1 ELSE NULL END AS INT) 
            AS OffEventRow
    FROM ordered_rows
    WHERE Row = 1
    UNION ALL
    SELECT o.Row, o.Identifier, o.Type, o.EventTime, o.DiscoveredDate, o.FileId,
        CAST(CASE WHEN o.Type = 'Went Off' AND f.Type = 'Went Off' 
            OR o.Type = 'Came On' THEN f.OffEventRow ELSE o.Row END AS INT)
    FROM ordered_rows o INNER JOIN filtered_rows f 
        ON o.Row = f.Row + 1 AND o.Identifier = f.Identifier
)
,on_events AS
(
   SELECT Identifier, OffEventRow, MAX(Row) AS OnRow
   FROM filtered_rows
   WHERE Type = 'Came On' AND OffEventRow IS NOT NULL
   GROUP BY OffEventRow, Identifier
)
SELECT f.Identifier, f.Type, f.EventTime, f.DiscoveredDate, f.FileId 
FROM filtered_rows f LEFT JOIN on_events o 
    ON f.Identifier = o.Identifier 
    AND f.Row = o.OnRow
WHERE f.Type = 'Went Off' AND f.Row = f.OffEventRow
OR f.Type = 'Came On' AND o.OnRow IS NOT NULL
ORDER BY f.Identifier, f.EventTime

Since there are no takers yet, I offer a guesswork answer: Yes, I think it's a bug.

Workaround: replace the illegal character once you know which one it is before converting the data to XML like this:

select xml_event_data = CONVERT(xml, replace(event_data,CHAR(3),'')), *
from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet', 
                '...\Log\error_*.xem', 
                null, 
                null
            )