Events
Get certified in Microsoft Fabric—for free!
19 Nov, 11 pm - 10 Dec, 11 pm
For a limited time, the Microsoft Fabric Community team is offering free DP-600 exam vouchers.
Prepare nowThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
Reads files created by the Extended Events asynchronous file target. One event, in XML format, is returned per row.
The Extended Events event_file
target stores the data it receives in a binary format that isn't human readable. Read the contents of the .xel
file with the sys.fn_xe_file_target_read_file
function. These files can also be read from Management Studio. For a walkthrough, see Quickstart: Extended Events.
Transact-SQL syntax conventions
sys.fn_xe_file_target_read_file ( path , mdpath , initial_file_name , initial_offset )
The path to the files to read. path can contain wildcards and include the name of a file. path is nvarchar(260) with no default. In the context of Azure SQL Database, this value is an HTTP URL to a file in Azure Storage.
The path to the metadata file that corresponds to the file or files specified by the path argument. mdpath is nvarchar(260) with no default.
In SQL Server 2012 (11.x) and later versions, you don't need this parameter. It was retained for backward compatibility, for log files generated in previous versions of SQL Server. In SQL Server 2016 (13.x) and later versions, this parameter can be given as NULL
, as .xem
files are no longer used.
The first file to read from path. initial_file_name is nvarchar(260) with no default. If NULL
is specified as the argument, all the files found in path are read.
Note
initial_file_name and initial_offset are paired arguments. If you specify a value for either argument, you must specify a value for the other argument.
Used to specify last offset read previously and skips all events up to the offset (inclusive). Event enumeration starts after the offset specified. initial_offset is bigint. If NULL
is specified as the argument, the entire file is read.
Column name | Data type | Description |
---|---|---|
module_guid |
uniqueidentifier | The event module GUID. Not nullable. |
package_guid |
uniqueidentifier | The event package GUID. Not nullable. |
object_name |
nvarchar(256) | The name of the event. Not nullable. |
event_data |
nvarchar(max) | The event contents, in XML format. Not nullable. |
file_name |
nvarchar(260) | The name of the file that contains the event. Not nullable. |
file_offset |
bigint | The offset of the block in the file that contains the event. Not nullable. |
timestamp_utc |
datetime2(7) | The date and time (UTC timezone) of the event. Not nullable. Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database. |
Reading large result sets by executing sys.fn_xe_file_target_read_file
in Management Studio might result in an error. Use the Results to File mode (in SQL Server Management Studio, Ctrl+Shift+F) to export large result sets to a human-readable file, to read the file with another tool instead.
SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x) accept trace results generated in XEL and XEM format. SQL Server 2012 (11.x) Extended Events only support trace results in XEL format. We recommend that you use Management Studio to read trace results in XEL format.
In Azure SQL Managed Instance or Azure SQL Database, store .xel
files in Azure Blob Storage. You can use sys.fn_xe_file_target_read_file
to read from extended event sessions you create yourself and store in Azure Blob Storage. For example walkthrough, review Create an event session with an event_file target in Azure Storage.
If you specify wildcard and/or a path for a local file system, you receive an error message similar to:
Msg 40538, Level 16, State 3, Line 15
A valid URL beginning with 'https://' is required as value for any filepath specified.
In SQL Server 2019 (15.x) and previous versions, requires VIEW SERVER STATE
permission on the server.
In SQL Server 2022 (16.x) and later versions, requires VIEW SERVER PERFORMANCE STATE
permission on the server.
For SQL Server 2014 (12.x) and previous versions, the following example gets all the rows from all the files, including both the .xel
and .xem
file. In this example, the file targets and metafiles are located in the trace folder in the C:\traces\
folder.
SELECT *
FROM sys.fn_xe_file_target_read_file('C:\traces\*.xel', 'C:\traces\metafile.xem', NULL, NULL);
In SQL Server 2016 (13.x) and later versions, the following example retrieves events inside all .xel
files in the default folder. The default location is \MSSQL\Log
within the installation folder of the instance.
SELECT *
FROM sys.fn_xe_file_target_read_file('*.xel', NULL, NULL, NULL);
In SQL Server 2017 (14.x) and later versions, the following example retrieves only data from the last day, from the built-in system_health session. The system_health session is an Extended Events session that is included by default with SQL Server. For more information, see Use the system_health session.
SELECT *
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE CAST(timestamp_utc AS DATETIME2(7)) > DATEADD(DAY, -1, GETUTCDATE());
Events
Get certified in Microsoft Fabric—for free!
19 Nov, 11 pm - 10 Dec, 11 pm
For a limited time, the Microsoft Fabric Community team is offering free DP-600 exam vouchers.
Prepare now