How to read SQL Server Trace Files using fn_trace_gettable function

There is a special function included in SQL Server called fn_trace_gettable to read a trace file.
Here is a simple T-SQL query to read the trace file 'C:\Trace_file.trc', that Profiler saved:

SELECT * FROM ::fn_trace_gettable('C:\Trace_file.trc', default)
order by starttime
GO

You can also filter the data that you query from the trace file, for example:

SELECT * FROM ::fn_trace_gettable('C:\Trace_file.trc', default)
WHERE
TextData like '%DELETE%'
OR TextData like '%TRUNCATE%'
OR TextData like '%DROP%'
order by starttime
GO

To import rows from 'C:\Trace_file.trc' trace file into 'trace_table' table in the AdventureWorks database:

USE AdventureWorks;
GO
SELECT * INTO trace_table
FROM fn_trace_gettable('C:\Trace_file.trc', default);
GO

To import rows from 'C:\Trace_file.trc' trace file into 'trace_table' table in the AdventureWorks database with Identity column:

USE AdventureWorks;
GO
SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO trace_table
FROM fn_trace_gettable('C:\Trace_file.trc', default);
GO

To see how to create a Server-side Trace, read related blog post:
The Server-side Trace

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">