{"id":558,"date":"2010-12-06T22:43:00","date_gmt":"2010-12-06T22:43:00","guid":{"rendered":"\/blogs\/jonathan\/post\/An-XEvent-a-Day-(6-of-31)-Targets-Week-asynchronous_file_target.aspx"},"modified":"2017-04-13T15:49:07","modified_gmt":"2017-04-13T19:49:07","slug":"an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/","title":{"rendered":"An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target"},"content":{"rendered":"

Yesterday\u2019s post, Targets Week – ring_buffer<\/a>, looked at the ring_buffer Target in Extended Events and how it outputs the raw Event data in an XML document.  Today I\u2019m going to go over the details of the other Target in Extended Events that captures raw Event data, the asynchronous_file_target.<\/p>\n

What is the asynchronous_file_target?<\/h2>\n

The asynchronous_file_target holds the raw format Event data in a proprietary binary file format that persists beyond server restarts and can be provided to another person via ftp or email for remote disconnected analysis of the events.  The asynchronous_file_target has two types of files that are associated with it, the log files which contain the Event data, and the metadata file which contains information about the Events contained in the log files, allowing correct parsing of the log files and the Events and associated Actions contained within them.  Depending on the options configured for the asynchronous_file_target, there may be multiple log files associated with a started Event Session, but there will only be one metadata file created for the duration of that Event Sessions collection.  Subsequent collections by the same Event Session, for example, stopping it and starting it again at a later time, will create a new metadata file associated with that collection by the Event Session.  These files exist as a set and must be maintained together for the log files to be read.<\/p>\n

Configuration Options<\/h2>\n

The asynchronous_file_target like the ring_buffer, has configuration options that can be found in the sys.dm_xe_object_columns DMV.<\/p>\n

\n
-- Target Configurable Fields\r\n<\/span>SELECT \r\n    <\/span>oc.<\/span>name AS <\/span>column_name,\r\n    <\/span>oc.<\/span>column_id,\r\n    <\/span>oc.<\/span>type_name<\/span>,\r\n    <\/span>oc.<\/span>capabilities_desc,\r\n    <\/span>oc.<\/span>description\r\nFROM <\/span>sys<\/span>.<\/span>dm_xe_packages <\/span>AS <\/span>p\r\nJOIN <\/span>sys<\/span>.<\/span>dm_xe_objects <\/span>AS <\/span>o \r\n    ON <\/span>p.<\/span>guid <\/span>= <\/span>o.<\/span>package_guid\r\nJOIN <\/span>sys<\/span>.<\/span>dm_xe_object_columns <\/span>AS <\/span>oc \r\n    ON <\/span>o.<\/span>name = <\/span>oc.<\/span>OBJECT_NAME \r\n    <\/span>AND <\/span>o.<\/span>package_guid = <\/span>oc.<\/span>object_package_guid\r\nWHERE <\/span>(<\/span>p.<\/span>capabilities IS NULL OR <\/span>p.<\/span>capabilities & <\/span>1 = <\/span>0)\r\n  AND (<\/span>o.<\/span>capabilities IS NULL OR <\/span>o.<\/span>capabilities & <\/span>1 = <\/span>0)\r\n  AND <\/span>o.<\/span>object_type = <\/span>'target'\r\n  <\/span>AND <\/span>o.<\/span>name = <\/span>'asynchronous_file_target'\r\n<\/span><\/pre>\n<\/blockquote>\n
 <\/p>\n
In SQL Server 2008, 2008R2, and SQL Server Denali CTP1, there are five configuration options for the asynchronous_file_target.  The filename specifies the path and name of the log files and is a required to add the asynchronous_file_target to an Event Session.  The max_file_size option functions the same as SQL Trace maxfilesize option, limiting the size of each file before rollover occurs.  The max_rollover_files option functions the same as the SQL Trace maxrolloverfiles option, specifying the number of rollover files to maintain in the file system, and can be used in conjunction with the max_file_size option to prevent the SQL Server from running out of disk space during Event collection.  The increment option is similar to the AutoGrowth settings for a database in SQL Server, and specifies the size in megabytes that the log files grow, allowing the files to grow incrementally and reducing the number of times a log file has to grow while Events are being dispatched and buffered to the Target.  The metadatafile option specifies the path and name of the metadata file for the target.<\/p>\n<\/p>\n
\"image\"<\/a> <\/p>\n
Notice that the only mandatory option for the file target is the filename for the log files.  When the asynchronous_file_target is used in an Event Session, if the metadatafile option is not explicitly set, the asynchronous_file_target will use the same path and filename specified in the filename option with a .xem extension for the metadata file automatically.  <\/p>\n
Understanding the Target Data Format<\/h2>\n
Like the ring_buffer, the asynchronous_file_target stores Event data in its raw format.  Inside the log files, the Event data is maintained in a binary format that minimizes the amount of space necessary to store the Events, maximizing the number of Events that can be stored inside the log files.  Unlike the ring_buffer target however, the asynchronous_file_target is queried not through the sys.dm_xe_session_targets DMV, but through the sys.fn_xe_file_target_read_file() DMF.  The sys.fn_xe_file_target_read_file() DMF requires four input parameters; @path which is the path, filename, and extension mask to the log files, @mdpath which is the path, filename, and extension mask to metadata file, @initial_file_name which is the exact path and filename of a file to start reading from and when specified requires the final parameter @initial_offset which is the offset inside that file from which to begin reading the events.<\/p>\n
The sys.fn_xe_file_target_read_file() DMF returns a single row for each instance of Event data that is contained inside of the log files being read.  The Event data is materialized into an XML document in the event_data column output by the DMF.when the Target information is queried using the sys.fn_xe_file_target_read_file() DMF, allowing it to be used for Event analysis.  Like the ring_buffer Target, the Event data returned by the sys.fn_xe_file_target_read_file() DMF is not schema bound, but it has exactly the same XML format as an individual <event> node in the ring_buffer Targets output making it very easy to parse the Events contained in either target with very similar XQuery\u2019s.<\/p>\n
Querying\/Parsing the Target Data<\/h2>\n
Since the asynchronous_file_target returns the Event data as XML, we have to do the same type of XQuery work to retrieve the Event data from it as we did with the ring_buffer target from yesterday.  After reading yesterday\u2019s post, Adam Machanic (Blog<\/a>|Twitter<\/a>) pointed out in a comment on Twitter<\/a> that the slow XML parsing is an optimizer bug that is handled with a derived table in his Extended Events Code Generator<\/a>.  Adam is absolutely correct, and if you attended my session on Extended Events this year at PASS you\u2019d recall that I didn\u2019t have performance issues in my demo\u2019s for querying the Target data generated by my demo\u2019s.  I used a very different parsing method in my PASS demo\u2019s than I showed yesterday, and I plan to cover that method in a later post in this series already.  However, if you want to see the gist of how to work around the performance issue take a look at the code output by Adam\u2019s code generator.<\/p>\n
I am going to reuse yesterday\u2019s demo as a basis for looking at the asynchronous_file_target for simplicity as well as to show the similarity of the XQuery used for querying the Event data.  The basic Event Session captures the error_reported Event and to trigger an error performs a SELECT against a non-existent table.<\/p>\n
\n
-- Create an Event Session to capture Errors Reported\r\n<\/span>CREATE EVENT SESSION <\/span>DemoPersistedEvents\r\nON SERVER\r\nADD EVENT <\/span>sqlserver.<\/span>error_reported\r\nADD TARGET <\/span>package0.<\/span>ring_buffer,\r\n<\/span>ADD TARGET <\/span>package0.<\/span>asynchronous_file_target(\r\n     <\/span>SET filename<\/span>=<\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\DemoPersistedEvents.xel'<\/span>)\r\n<\/span>WITH <\/span>(<\/span>MAX_DISPATCH_LATENCY = <\/span>1 SECONDS)\r\n<\/span>GO\r\n<\/span>-- Alter the Event Session and Start it.\r\n<\/span>ALTER EVENT SESSION <\/span>DemoPersistedEvents\r\nON SERVER\r\nSTATE<\/span>=<\/span>START\r\nGO\r\n<\/span>-- SELECT from a non-existent table to create Event\r\n<\/span>SELECT <\/span>*\r\n<\/span>FROM master<\/span>.<\/span>schema_doesnt_exist.<\/span>table_doesnt_exist\r\nGO\r\n<\/span>-- Drop the Event to halt Event collection\r\n<\/span>ALTER EVENT SESSION <\/span>DemoPersistedEvents\r\nON SERVER\r\nDROP EVENT <\/span>sqlserver.<\/span>error_reported\r\nGO<\/span><\/pre>\n<\/blockquote>\n
<\/a><\/p>\n
The first thing we need to know to query our asynchronous_file_target is the filename and metafilename for the files that we want to query from.  If the event session is active and running, we can get this information by querying the Active Session DMV\u2019s.<\/p>\n
\n
SELECT \r\n    <\/span>soc.<\/span>column_name,\r\n    <\/span>soc.<\/span>column_value\r\nFROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>s\r\nJOIN <\/span>sys<\/span>.<\/span>dm_xe_session_object_columns <\/span>soc\r\n    ON <\/span>s.<\/span>address <\/span>= <\/span>soc.<\/span>event_session_address\r\nWHERE <\/span>s.<\/span>name = <\/span>'DemoPersistedEvents'\r\n  <\/span>AND <\/span>soc.<\/span>object_name <\/span>= <\/span>'asynchronous_file_target'<\/span><\/pre>\n<\/blockquote>\n
\"image\"<\/a> <\/p>\n
Notice that the metatdatafile option is NULL, meaning that we were lazy and didn\u2019t explicitly define the metadata file information in our Event Session so now we have to figure it out in order to query the target data from the log files.  One way to find the information would be to open up the path on the server to the log file that was specified:<\/p>\n
\"image\"<\/a> <\/p>\n
Notice that the Extended Events Engine automatically created a metadata file with the same name as the log file, but a different extension, .xem.  Also notice that the file names for both the log file and the metadata file have changed from what was actually defined in the Event Session.  The Engine adds a _0_ and a long integer value that represents the number of milliseconds between January 1, 1600 and the date and time that the file was generated by the Extended Events Engine.  Subsequent files will have a different long integer value that is larger in value allowing you to easily sort the log files from oldest to newest or vice versa.  To query the data contained in the log files, you have two options.  First you can explicitly provide the filenames as shown above, or you can use wildcards in the names and the engine will find the correct matching files and begin reading them.<\/p>\n
\n
DECLARE <\/span>@path nvarchar<\/span>(<\/span>260), <\/span>@mdpath nvarchar<\/span>(<\/span>260)\r\n\r\n<\/span>-- Get the log file name and substitute * wildcard in\r\n<\/span>SELECT \r\n    <\/span>@path = LEFT(<\/span>column_value, <\/span>LEN<\/span>(<\/span>column_value)-<\/span>CHARINDEX<\/span>(<\/span>'.'<\/span>, <\/span>REVERSE<\/span>(<\/span>column_value))) \r\n        + <\/span>'*' \r\n        <\/span>+ RIGHT(<\/span>column_value, <\/span>CHARINDEX<\/span>(<\/span>'.'<\/span>, <\/span>REVERSE<\/span>(<\/span>column_value))-<\/span>1)\r\n<\/span>FROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>s\r\nJOIN <\/span>sys<\/span>.<\/span>dm_xe_session_object_columns <\/span>soc\r\n    ON <\/span>s.<\/span>address <\/span>= <\/span>soc.<\/span>event_session_address\r\nWHERE <\/span>s.<\/span>name = <\/span>'DemoPersistedEvents'\r\n  <\/span>AND <\/span>soc.<\/span>object_name <\/span>= <\/span>'asynchronous_file_target'\r\n  <\/span>AND <\/span>soc.<\/span>column_name = <\/span>'filename'\r\n\r\n<\/span>-- Get the metadata file name and substitute * wildcard in \r\n<\/span>SELECT \r\n    <\/span>@mdpath = LEFT(<\/span>column_value, <\/span>LEN<\/span>(<\/span>column_value)-<\/span>CHARINDEX<\/span>(<\/span>'.'<\/span>, <\/span>REVERSE<\/span>(<\/span>column_value))) \r\n        + <\/span>'*' \r\n        <\/span>+ RIGHT(<\/span>column_value, <\/span>CHARINDEX<\/span>(<\/span>'.'<\/span>, <\/span>REVERSE<\/span>(<\/span>column_value))-<\/span>1)\r\n<\/span>FROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>s\r\nJOIN <\/span>sys<\/span>.<\/span>dm_xe_session_object_columns <\/span>soc\r\n    ON <\/span>s.<\/span>address <\/span>= <\/span>soc.<\/span>event_session_address\r\nWHERE <\/span>s.<\/span>name = <\/span>'DemoPersistedEvents'\r\n  <\/span>AND <\/span>soc.<\/span>object_name <\/span>= <\/span>'asynchronous_file_target'\r\n  <\/span>AND <\/span>soc.<\/span>column_name = <\/span>' metadatafile'\r\n\r\n<\/span>-- Set the metadata filename if it is NULL to the log file name with xem extension\r\n<\/span>SELECT <\/span>@mdpath = <\/span>ISNULL<\/span>(<\/span>@mdpath, \r\n                        LEFT(<\/span>@path, <\/span>LEN<\/span>(<\/span>@path)-<\/span>CHARINDEX<\/span>(<\/span>'*'<\/span>, <\/span>REVERSE<\/span>(<\/span>@path))) \r\n                        + <\/span>'*xem'<\/span>)\r\n\r\n<\/span>-- Query the Event data from the Target.\r\n<\/span>SELECT\r\n    <\/span>module_guid,\r\n    <\/span>package_guid,\r\n    <\/span>object_name<\/span>,\r\n    <\/span>event_data,\r\n    <\/span>file_name<\/span>,\r\n    <\/span>file_offset\r\nFROM <\/span>sys<\/span>.<\/span>fn_xe_file_target_read_file(<\/span>@path, <\/span>@mdpath, null, null)\r\n<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
 <\/p>\n
\"image\"<\/a> <\/p>\n
 <\/p>\n
The DMF outputs the module_guid, package_guid, and object_name associated with the Event, the event_data as a XML document, but in string format requiring that it be CAST\/CONVERT\u2019d to XML for parsing, the file_name of the log file that the Event data was read from and the file_offset inside the file for the event.  Using a CAST to XML and performing a CROSS APPLY of the <event> nodes and the same XQuery\u2019s as in yesterday\u2019s post we can query the Event data from the asynchronous_file_target.<\/p>\n
\n
-- Query the Event data from the Target.\r\n<\/span>SELECT \r\n    <\/span>n.<\/span>value(<\/span>'(@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name,\r\n    <\/span>n.<\/span>value(<\/span>'(@package)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>package_name,\r\n    <\/span>n.<\/span>value(<\/span>'(@id)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>id,\r\n    <\/span>n.<\/span>value(<\/span>'(@version)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS version<\/span>,\r\n    <\/span>DATEADD<\/span>(<\/span>hh, \r\n            <\/span>DATEDIFF<\/span>(<\/span>hh, <\/span>GETUTCDATE<\/span>(), <\/span>CURRENT_TIMESTAMP<\/span>), \r\n            <\/span>n.<\/span>value(<\/span>'(@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp],\r\n    <\/span>n.<\/span>value(<\/span>'(data[@name="error"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>error,\r\n    <\/span>n.<\/span>value(<\/span>'(data[@name="severity"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>severity,\r\n    <\/span>n.<\/span>value(<\/span>'(data[@name="duration"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as state<\/span>,\r\n    <\/span>n.<\/span>value(<\/span>'(data[@name="user_defined"]\/value)[1]'<\/span>, <\/span>'varchar(5)'<\/span>) <\/span>as <\/span>user_defined,\r\n    <\/span>n.<\/span>value(<\/span>'(data[@name="message"]\/value)[1]'<\/span>, <\/span>'varchar(max)'<\/span>) <\/span>as message\r\nFROM \r\n<\/span>(<\/span>SELECT\r\n    <\/span>CAST<\/span>(<\/span>event_data AS XML<\/span>) <\/span>AS <\/span>event_data\r\n FROM <\/span>sys<\/span>.<\/span>fn_xe_file_target_read_file(<\/span>@path, <\/span>@mdpath, null, null)\r\n) <\/span>as <\/span>tab\r\nCROSS APPLY <\/span>event_data.<\/span>nodes(<\/span>'event'<\/span>) <\/span>as <\/span>q(<\/span>n)<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
Like the ring_buffer Target, the asynchronous_file_target also has an entry in sys.dm_xe_session_targets, but instead of returning the Event data, it returns information about the targets operation.<\/p>\n
\n
select \r\n    <\/span>target_data.<\/span>value(<\/span>'(FileTarget\/@truncated)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>truncated,\r\n    <\/span>target_data.<\/span>value(<\/span>'(FileTarget\/Buffers\/@logged)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>logged,\r\n    <\/span>target_data.<\/span>value(<\/span>'(FileTarget\/Buffers\/@dropped)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>dropped\r\nFROM\r\n<\/span>(<\/span>SELECT <\/span>CAST<\/span>(<\/span>target_data AS XML<\/span>) <\/span>AS <\/span>target_data\r\nFROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>AS <\/span>s \r\nJOIN <\/span>sys<\/span>.<\/span>dm_xe_session_targets <\/span>AS <\/span>t \r\n    ON <\/span>t.<\/span>event_session_address = <\/span>s.<\/span>address\r\nWHERE <\/span>s.<\/span>name = <\/span>'DemoPersistedEvents'\r\n  <\/span>AND <\/span>t.<\/span>target_name = <\/span>'asynchronous_file_target'\r\n<\/span>) <\/span>as <\/span>tab<\/pre>\n
  <\/a><\/p><\/blockquote>\n
The file_name and file_offset information in the the sys.fn_xe_file_target_read_file output can be used to perform differential reads from the asynchronous_file_target.  To demonstrate this we can create an Event Session that will capture a lot of Events in a short period of time. <\/p>\n
(Note: I wouldn\u2019t create an unfiltered Event Session on the starting and completed events like this on a production server without first evaluating its potential impact.  While this should be safe, if it causes you a problem, its your server not mine.)<\/strong><\/p>\n
\n
IF <\/span>EXISTS(<\/span>SELECT <\/span>* <\/span>FROM <\/span>sys<\/span>.<\/span>server_event_sessions <\/span>WHERE <\/span>name=<\/span>'FileTargetDemo'<\/span>)\r\n    <\/span>DROP EVENT SESSION <\/span>[FileTargetDemo] ON SERVER<\/span>;\r\n<\/span>CREATE EVENT SESSION <\/span>[FileTargetDemo]\r\nON SERVER\r\nADD EVENT <\/span>sqlserver.<\/span>sql_statement_starting,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sql_statement_completed,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sp_statement_starting,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sp_statement_completed,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>rpc_starting,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>rpc_completed,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>module_start,\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>module_end\r\nADD TARGET <\/span>package0.<\/span>asynchronous_file_target(\r\n     <\/span>SET filename<\/span>=<\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo.xel'<\/span>, \r\n         <\/span>metadatafile=<\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo.xem'<\/span>,\r\n         <\/span>max_file_size = <\/span>5,\r\n         <\/span>max_rollover_files = <\/span>5)\r\n<\/span>WITH<\/span>(<\/span>MAX_DISPATCH_LATENCY = <\/span>5SECONDS)\r\n<\/span>GO\r\n\r\n<\/span>-- Start the Event Collection\r\n<\/span>ALTER EVENT SESSION <\/span>[FileTargetDemo]\r\nON SERVER\r\nSTATE<\/span>=<\/span>STOP\r\nGO\r\n\r\n<\/span>-- Take a pause and allow events to be generated\r\n\r\n\r\n-- Query the target data from the files.\r\n<\/span>SELECT \r\n    <\/span>object_name<\/span>,\r\n    <\/span>CAST<\/span>(<\/span>event_data as xml<\/span>) <\/span>as <\/span>event_data,\r\n    <\/span>file_name<\/span>, \r\n    <\/span>file_offset\r\nFROM <\/span>sys<\/span>.<\/span>fn_xe_file_target_read_file(<\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo*xel'<\/span>, \r\n            <\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo*xem'<\/span>, \r\n            null,\r\n            null)<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
If you scroll through the output to where the file_offset changes, you can grab the file_name and file_offset for the last event in the first file_offset.<\/p>\n
\"image\"<\/a> <\/p>\n
Then requery the target passing that file_name and file_offset into the @initial_file_name and @intitial_offset parameters of the sys.fn_xe_file_target_read_file DMF to have the DMF begin reading from the last entry of the provided offset forward.<\/p>\n
\n
-- Query the target data from the files.\r\n<\/span>SELECT \r\n    <\/span>object_name<\/span>,\r\n    <\/span>CAST<\/span>(<\/span>event_data as xml<\/span>) <\/span>as <\/span>event_data,\r\n    <\/span>file_name<\/span>, \r\n    <\/span>file_offset\r\nFROM <\/span>sys<\/span>.<\/span>fn_xe_file_target_read_file(<\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo*xel'<\/span>, \r\n            <\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo*xem'<\/span>,             \r\n            <\/span>'D:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Log\\FileTargetDemo_0_129360796797990000.xel'<\/span>,\r\n            <\/span>0)<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
\"image\"<\/a> <\/p>\n
If you\u2019ve run the demo\u2019s in this blog post to this point, don\u2019t forget to cleanup the system.<\/p>\n
\n
IF <\/span>EXISTS(<\/span>SELECT <\/span>* <\/span>FROM <\/span>sys<\/span>.<\/span>server_event_sessions <\/span>WHERE <\/span>name=<\/span>'FileTargetDemo'<\/span>)\r\n    <\/span>DROP EVENT SESSION <\/span>[FileTargetDemo] ON SERVER<\/span>;\r\n<\/span>GO\r\nIF <\/span>EXISTS(<\/span>SELECT <\/span>* <\/span>FROM <\/span>sys<\/span>.<\/span>server_event_sessions <\/span>WHERE <\/span>name=<\/span>'DemoPersistedEvents'<\/span>)\r\n    <\/span>DROP EVENT SESSION <\/span>[DemoPersistedEvents] ON SERVER<\/span>;\r\n<\/span>GO<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
Considerations for Usage<\/h2>\n
The asynchronous_file_target will probably be the preferred target for most people interested in performing long term analysis of Events collected, or performing short term analysis using an Event Session that is expected to generate a large number of events and event loss due to the FIFO nature of the ring_buffer is not acceptable.  However, there are a couple of considerations associated with this target.  The first is that the log files and metadata file are a set, and have to be maintained together.  If you send someone a log file without the metadata file, they won\u2019t be able to read the information contained in the log file.  The second consideration associated with this target is that the only way to read the information contained inside of the log files, as of the date of this blog post being published, is to copy them to a system that is running SQL Server 2008 or 2008R2 and query the files using the there is no way to retrieve the information contained inside of the log files, without querying the sys.fn_xe_file_target_read_file() DMF using TSQL.  <\/p>\n
What\u2019s next?<\/h2>\n
Now that we have looked at the asynchronous_file_target Target, in the next post we\u2019ll look at the bucketizer Targets which can be used to group occurrences of Events based on the Event data being returned.<\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday\u2019s post, Targets Week – ring_buffer, looked at the ring_buffer Target in Extended Events and how it outputs the raw Event data in an XML document.  Today I\u2019m going to go over the details of the other Target in Extended Events that captures raw Event data, the asynchronous_file_target. What is the asynchronous_file_target? The asynchronous_file_target holds […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,38,45],"tags":[],"class_list":["post-558","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008","category-xevent-a-day-series"],"yoast_head":"\nAn XEvent a Day (6 of 31) - Targets Week - asynchronous_file_target - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"An XEvent a Day (6 of 31) - Targets Week - asynchronous_file_target - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Yesterday\u2019s post, Targets Week – ring_buffer, looked at the ring_buffer Target in Extended Events and how it outputs the raw Event data in an XML document.  Today I\u2019m going to go over the details of the other Target in Extended Events that captures raw Event data, the asynchronous_file_target. What is the asynchronous_file_target? The asynchronous_file_target holds […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-06T22:43:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:49:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday6of31targetsweekasynchronous\/4bff6370\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target\",\"datePublished\":\"2010-12-06T22:43:00+00:00\",\"dateModified\":\"2017-04-13T19:49:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/\"},\"wordCount\":1889,\"commentCount\":5,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday6of31targetsweekasynchronous\\\/4bff6370\\\/image_thumb.png\",\"articleSection\":[\"Extended Events\",\"SQL Server 2008\",\"XEvent a Day Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/\",\"name\":\"An XEvent a Day (6 of 31) - Targets Week - asynchronous_file_target - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday6of31targetsweekasynchronous\\\/4bff6370\\\/image_thumb.png\",\"datePublished\":\"2010-12-06T22:43:00+00:00\",\"dateModified\":\"2017-04-13T19:49:07+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday6of31targetsweekasynchronous\\\/4bff6370\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday6of31targetsweekasynchronous\\\/4bff6370\\\/image_thumb.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/extended-events\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"An XEvent a Day (6 of 31) - Targets Week - asynchronous_file_target - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/","og_locale":"en_US","og_type":"article","og_title":"An XEvent a Day (6 of 31) - Targets Week - asynchronous_file_target - Jonathan Kehayias","og_description":"Yesterday\u2019s post, Targets Week – ring_buffer, looked at the ring_buffer Target in Extended Events and how it outputs the raw Event data in an XML document.  Today I\u2019m going to go over the details of the other Target in Extended Events that captures raw Event data, the asynchronous_file_target. What is the asynchronous_file_target? The asynchronous_file_target holds […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/","og_site_name":"Jonathan Kehayias","article_published_time":"2010-12-06T22:43:00+00:00","article_modified_time":"2017-04-13T19:49:07+00:00","og_image":[{"url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday6of31targetsweekasynchronous\/4bff6370\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target","datePublished":"2010-12-06T22:43:00+00:00","dateModified":"2017-04-13T19:49:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/"},"wordCount":1889,"commentCount":5,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday6of31targetsweekasynchronous\/4bff6370\/image_thumb.png","articleSection":["Extended Events","SQL Server 2008","XEvent a Day Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/","name":"An XEvent a Day (6 of 31) - Targets Week - asynchronous_file_target - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday6of31targetsweekasynchronous\/4bff6370\/image_thumb.png","datePublished":"2010-12-06T22:43:00+00:00","dateModified":"2017-04-13T19:49:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#primaryimage","url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday6of31targetsweekasynchronous\/4bff6370\/image_thumb.png","contentUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday6of31targetsweekasynchronous\/4bff6370\/image_thumb.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"An XEvent a Day (6 of 31) – Targets Week – asynchronous_file_target"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/558","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=558"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/558\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=558"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=558"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=558"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}