Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

When running a trace in SQL Server Profiler, it is bad practice to save the trace information directly into a SQL Server table. Writing trace data directly to a table uses a lot or resources and this can greatly errode server performance. However, it is possible to load the contents of a file once a trace has been captured.

Find the path of any existing traces by executing the following:-

select path 
from sys.traces

Once the path and file name are known, run the following SQL:-

-- Load contents of trace into a SQL temporary table
-- Do not specify log numbers to load all log files for a trace
SELECT * INTO ##temp_trc
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default);

SELECT * FROM ##temp_trc

The above example loads all trace files like log%.trc [log1.trc, log2.trc etc] into a temporary table. You can of course load into a physically stored table by removing the hashes. If you want to load data from just one trace file from a group of rolled over trace files then specify the number on the end [e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log7.trc].

Advertisements

Tags: , , ,

%d bloggers like this: