TechNet
Export (0) Print
Expand All

sys.fn_xe_file_target_read_file (Transact-SQL)

 

Updated: June 24, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Reads files that are created by the Extended Events asynchronous file target. One event, in XML format, is returned per row.

System_CAPS_ICON_warning.jpg Warning


SQL Server 2008and SQL Server 2008 R2 accepts trace results generated in XEL and XEM format. SQL Server 2012 Extended Events only supports trace results in XEL format. We recommend that you use SQL Server Management Studio to read trace results in XEL format.

Topic link icon Transact-SQL Syntax Conventions

  
sys.fn_xe_file_target_read_file ( path, mdpath, initial_file_name, initial_offset )  

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. Starting with SQL Server 2016, this parameter can be given as null.

System_CAPS_ICON_note.jpg Note


SQL Server 2012 does not require the mdpath parameter. However, it is maintained for backward compatibility for log files generated in previous versions of SQL Server.

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.

System_CAPS_ICON_note.jpg 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.

Column nameData typeDescription
module_guiduniqueidentifierThe event module GUID. Is not nullable.
package_guiduniqueidentifierThe event package GUID. Is not nullable.
object_namenvarchar(256)The name of the event. Is not nullable.
event_datanvarchar(max)The event contents, in XML format. Is not nullable.
file_namenvarchar(260)The name of the file that contains the event. Is not nullable.
file_offsetbigintThe offset of the block in the file that contains the event. Is not nullable.

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 (Ctrl+Shift+F) to export large result sets to a file and read the file with another tool instead.

Requires VIEW SERVER STATE permission on the server.

A. Retrieving data from file targets

The following example gets all the rows from all the files. In this example the file targets and metafiles are located in the trace folder on the C:\ drive.

SELECT * FROM sys.fn_xe_file_target_read_file('C:\traces\*.xel', 'C:\traces\metafile.xem', null, null);  

Extended Events Dynamic Management Views
Extended Events Catalog Views (Transact-SQL)
Extended Events

Community Additions

ADD
Show:
© 2016 Microsoft