Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In SQL Server, how to move/import a multiple .trc files to a trace table

Tags:

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.

like image 888
frankadelic Avatar asked May 06 '10 21:05

frankadelic


People also ask

How do I trace a file in SQL Server?

To open the trace file: Open SQL Profiler, Start > Programs > Microsoft SQL Server > Profiler. Select File > Open >Trace File. Navigate to the directory where the trace file was stored and open it.

How many ways can SQL Server trace an instance?

SQL Server provides two ways to trace an instance of SQL Server: you can trace with SQL Server Profiler, or you can trace using system stored procedures.

Where are SQL trace files stored?

The default trace log is stored by default in the \MSSQL\LOG directory using a rollover trace file. The base file name for the default trace log file is log. trc . In a typical installation of SQL Server, the default trace is enabled and thus becomes TraceID 1.


1 Answers

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.

like image 144
Paul Kearney - pk Avatar answered Sep 25 '22 14:09

Paul Kearney - pk