22

I have a set of rollover .trc files recorded with Sql Profiler.

mytrace.trc

mytrace_1.trc

mytrace_2.trc

mytrace_3.trc

I can import the first one using this command:

use [my-database]
SELECT * INTO trace_folder
FROM::fn_trace_gettable('C:\mytrace.trc', 4)

However, this only appears to load the first file, not all four.

frankadelic
  • 20,543
  • 37
  • 111
  • 164

2 Answers2

32

You'll want to use fn_trace_gettable:

From http://msdn.microsoft.com/en-us/library/ms188425.aspx:

USE AdventureWorks;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('c:\temp\mytrace.trc', default);
GO

Also, a warning from the documentation:

Be aware that the fn_trace_gettable function will not load rollover files (when this option is specified by using the number_files argument) where the original trace file name ends with an underscore and a numeric value. (This does not apply to the underscore and number that are automatically appended when a file rolls over.) As a workaround, you can rename the trace files to remove the underscores in the original file name. For example, if the original file is named Trace_Oct_5.trc and the rollover file is named Trace_Oct_5_1.trc, you can rename the files to TraceOct5.trc and TraceOct5_1.trc.

Paul Kearney - pk
  • 5,435
  • 26
  • 28
  • 1
    updated my question for clarity -- I am interested in multiple rollover .trc files – frankadelic May 06 '10 at 21:46
  • 5
    Passing "default" as the second parameter should load all trace files sequentially, unless the initial tracefile name ends with an underscore and a number (which in your example, it does not). Not sure why you are not getting them all loaded. Are they all in the same folder? – Paul Kearney - pk May 06 '10 at 22:08
  • ah, that was the issue. (my original files were actually named mytrace_20100420.trc etc...) Thanks! – frankadelic May 06 '10 at 22:22
  • 1
    @frankadelic, how did you get it to work? I've got the same situation, but when I try to load up my file, i goet "file deither does not exist or is not a recognizable trace file". I know the file is there, but for some reason it can't load it. – russds Apr 18 '12 at 16:44
  • @russds - It would seem that the "..is not a recognizable trace file" may be pertinent here. Anyways, think the community would suggest putting this in a seperate question, as it is not directly related to the question above. – user66001 Sep 26 '12 at 12:23
  • @PaulKearney-pk I think it would be worth noting the "underscore and a number" limitation directly in the answer. – Holistic Developer May 14 '14 at 15:46
  • Yes, definitely put a note about _ in file names, I stumbled on it too. – Victor Zakharov Nov 06 '17 at 19:40
1

From SQL 2008 BOL ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/c2590159-6ec5-4510-81ab-e935cc4216cd.htm

Be aware that the fn_trace_gettable function will not load rollover files (when this option is specified by using the number_files argument) where the original trace file name ends with an underscore and a numeric value. (This does not apply to the underscore and number that are automatically appended when a file rolls over.) As a workaround, you can rename the trace files to remove the underscores in the original file name. For example, if the original file is named Trace_Oct_5.trc and the rollover file is named Trace_Oct_5_1.trc, you can rename the files to TraceOct5.trc and TraceOct5_1.trc.

This was the problem I had. My Server Side Trace names are _Purpose.trc. What was I thinking when I embedded "" in teh file name :)

Ray
  • 197
  • 1
  • 2