Reading trace files using SQL virtual funtion

Question

Reading trace files using SQL virtual funtion

pks1985

SSC Journeyman

Points: 81
More actions
November 12, 2008 at 9:34 pm

#193776

Hi,
The trace information from the trace files can be read through the virtual funtion sys.fn_xe_file_target_read_file in SQL 2008 using the code below,
DROP table xTable
CREATE TABLE xTable
( xTable_ID INT IDENTITY PRIMARY KEY,
xCol XML ) ;
INSERT INTO xTable ( xCol )
select cast(event_data as xml) from sys.fn_xe_file_target_read_file
('c:\temp\wait_*.etx','c:\temp\wait_*.mta',null,null)
The rows are returned in XML format and inserted into a table.
But the concern here is that when these trace files are in use i.e. when it is being updated by other means ( say using extended events concepts), is it possible to read the data at that moment?
Or will there be any lock over these files which will prevent the funtion from reading the data?
Kindly provide me suggestions on this.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

Last time I tried, the files were locked. You can set up a rollover so that you're getting a new file every so often and the old files are available.

Alternately, for immediate understanding of what's occuring, go to the dynamic management views and functions. For index information, cumulative since the last time SQL Server was restarted, use sys.dm_db_index_usage_stats. To see the plans currently in cache, you can use sys.dm_exec_cached_plans. To see current locking stats, sys.dm_index_operational_stats. To see current running functions, including blocking processes, reads, writes, cpu, sys.dm_exec_requests. And finally to see aggregate performance information for queries that are currently in cache, go to sys.dm_exec_query_stats.

Trace is fantastic for historical info, but if you need to know what's occurring NOW, I'd strongly recommend hitting the DMV's & DMF's I listed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

I don't usually resurrect old threads like this, but I happened on this one while doing some other research, and thought I'd comment.

In SQL 2008, you can query the asynchronous_file target files, while the Extended Event Session is active and running. The files will be locked at the file system, but since SQL Server is writing to the file, it is already the owner of the file handle that locks it. Since you can only read the data from the Async File Target using the DMF in SQL Server 2008, no external API exists currently, SQL can still read the file without any issue.