Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server Azure SQL Database Azure SQL Managed Instance
Reads the event log XEL files created by the Extended Events event_file target. Each row in the result set represents an event. Event data is returned in XML format.
XEL files can also be read by SQL Server Management Studio. For a walkthrough, see Quickstart: Extended Events.
Transact-SQL syntax conventions
Syntax
sys.fn_xe_file_target_read_file ( path , mdpath , initial_file_name , initial_offset ) Arguments
path
The path to the files to read. path is nvarchar(260) with no default.
When used with files in the local file system, path must include the name of an event session log file. The file name can contain
*as a wildcard to read data from multiple files.When used with blobs in an Azure Storage container, path is an HTTP URL constructed by the concatenation of two parts:
The path to an Azure Storage storage container, followed by a slash (
/).A common prefix for the names of blobs in the container that should be read. To read a single blob, use the full name of the blob.
For example, using
https://<storage-account-name>.blob.core.windows.net/container-name>/xe_sessionas path retrieves data from all blobs with names starting withxe_session, and regardless of the remainder of the blob name including the extension. Wildcards can't be specified.
mdpath
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's 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.
initial_file_name
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.
The file name must be a value returned in the result set of a sys.fn_xe_file_target_read_file function call with the same path.
initial_offset
Used to specify last offset read previously. 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.
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.
Table returned
| 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, Azure SQL Database, and Azure SQL Managed Instance. |
Remarks
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.
Azure SQL
In Azure SQL Managed Instance or Azure SQL Database, the files created by the event_file target are always stored as blobs in an Azure Storage container.
You can use sys.fn_xe_file_target_read_file to read data from these blobs if a credential allowing access to the Azure Storage container exists. For a walkthrough, review Create an event session with an event_file target in Azure Storage.
If you specify a local file system path, 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. Permissions
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.
Examples
A. Retrieve data from files in the local file system
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()); B. Retrieve data from blobs in an Azure Storage container
Read data from all blobs in the container with names starting with xe_session_.
SELECT * FROM sys.fn_xe_file_target_read_file( 'https://<storage-account-name>.blob.core.windows.net/<container-name>/xe_session_', NULL, NULL, NULL ); Read data from the xe_session_0_133614763336380000.xel blob.
SELECT * FROM sys.fn_xe_file_target_read_file( 'https://<storage-account-name>.blob.core.windows.net/<container-name>/xe_session_0_133614763336380000.xel', NULL, NULL, NULL ); Read data from the xe_session_0_133614763336380000.xel blob starting with offset 33280.
SELECT * FROM sys.fn_xe_file_target_read_file( 'https://<storage-account-name>.blob.core.windows.net/<container-name>/xe_session_', NULL, 'https://<storage-account-name>.blob.core.windows.net/<container-name>/xe_session_0_133614763336380000.xel', 33280 );