Edit

sys.fn_xe_file_target_read_file (Transact-SQL)

  • Article
  • 4 minutes to read

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Reads files that are 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 is not 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 in SQL Server.

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 can contain wildcards and include the name of a file. path is nvarchar(260). There is no default. In the context of Azure SQL Database, this value is an HTTP URL to a file in Azure Storage.

mdpath

The path to the metadata file that corresponds to the file or files specified by the path argument. mdpath is nvarchar(260). There is no default. SQL Server 2012 (11.x) does not require the mdpath parameter. However, it is maintained for backward compatibility for log files generated in previous versions of SQL Server. Starting with SQL Server 2016 (13.x) , 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). There is 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.

initial_offset

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 will be read.

Table Returned

Column name Data type Description
module_guid uniqueidentifier The event module GUID. Is not nullable.
package_guid uniqueidentifier The event package GUID. Is not nullable.
object_name nvarchar(256) The name of the event. Is not nullable.
event_data nvarchar(max) The event contents, in XML format. Is not nullable.
file_name nvarchar(260) The name of the file that contains the event. Is not nullable.
file_offset bigint The offset of the block in the file that contains the event. Is not nullable.
timestamp_utc datetime2(7) Applies to: SQL Server 2017 (14.x) and later and Azure SQL Database.

The date and time (UTC timezone) of the event. Is not nullable.

Remarks

Reading large result sets by executing sys.fn_xe_file_target_read_file in Management Studio may result in an error. Use the Results to File mode (in SSMS, 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, 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 Event File target code for extended events in Azure SQL Database and Azure SQL Managed Instance.

If you specify wildcard and/or a path for a local file system, you will 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

Requires VIEW SERVER STATE permission on the server.

Examples

A. Retrieving data from file targets

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, 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) or later, 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())

See also

Next steps