18 September,2019 by Tom Collins
Question: I'm trying to read the Microsoft SQL Server Extended Event Log File - xel file for the SQL Server Extended Events system_health extended event.
Just opening it up in Notepad - produces human unreadable output . How can I read the Extended Event Log File output file , using t0-sql?
Answer: Microsoft have provided various methods and SQL functions which can read the Extended Event Log File file . There are multiple options. It is possible to list out the records within the Log file - this option will return a recordset, as xml output.
You may want to go one step further - and parse the xml file , to create an output in tabular format.
Option 1 - Read the Extended Event Log File output file and return as xml format
SQL Server provide a function - sys.fn_xe_file_target_read_file . This function reads the files created by the Extended Events asynchronous file target. There is an event returned by row . The event in the row utilises an XML format
This sample query uses the information gathered in the @path variable to then pass it through to the sys.fn_xe_file_target_read_file function. Execute this code on a SQL Server and you should see a 2 columns : Event_Data and object_name. In the query below - I'm just checking for 'wait_info'
DECLARE @path NVARCHAR(260); --retrieve the local path of system_health files SELECT @path = dosdlc.path FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc; SET @path = @path + N'system_health_*'; SELECT CAST(fx.event_data AS XML) AS Event_Data, fx.object_name FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL) AS fx WHERE fx.object_name = 'wait_info';
Option 2 - Read the Extended Event Log File output file and PARSE the returned xml format rows
This option extends the manipulation of the xml data produced by sys.fn_xe_file_target_read_file . The query below continues to use sys.fn_xe_file_target_read_file but now there is an added CROSS APPLY to the data nodes
DECLARE @path NVARCHAR(260); --retrieve the local path of system_health files SELECT @path = dosdlc.path FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc; SET @path = @path + N'system_health_*'; SELECT n.value('(@name)[1]', 'varchar(50)') as event_name, n.value('(@package)[1]', 'varchar(50)') AS package_name, n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp], n.value('(data[@name="duration"]/value)[1]', 'int') as duration, n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu, n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads, n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads, n.value('(data[@name="writes"]/value)[1]', 'int') as writes, n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count, n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count, n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number, n.value('(data[@name="offset"]/value)[1]', 'int') as offset, n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end, n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement, n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name FROM(select cast(event_data as XML) as event_data FROM sys.fn_xe_file_target_read_file(@path, null, null, null)) fx CROSS APPLY fx.event_data.nodes('event') as q(n) WHERE n.value('(@package)[1]', 'varchar(50)') = 'sqlos';
These are some simple examples . If you any other techniques and approaches , let me know
Read more on XML parsing
Shredding XML with Powershell and SQL Server (SQL Server DBA)
Troubleshoot SQL Security Error Ring Buffer Recorded - System ...
OpenXML - more than 8000 characters (SQL Server DBA)
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |