How to read and parse the Microsoft SQL Server extended event log file xel file

18 September,2019 by Jack Vamvas

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)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on How to read and parse the Microsoft SQL Server extended event log file xel file


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer