SQL Server Performance Forum – Threads Archive

Trace file

I have generated a profiler trace file and want to load this into a table. I was wondering, how could i achieve this? Appreciated some help. Thanks,

---

Why don’t you save the file to Table while running the profiler? You can choose this option while setting up for profiler. Name
———
Dilli Grg (1 row(s) affected)

---

If it’s a production server, this (running to table while tracing) is a very bad idea. If you have a trace file already saved, you can load it into a table using the fn_trace_gettable function. Check Books Online for examples of how to read/load trace files via the fn_trace_* functions.

---

From BOL…
SELECT * FROM ::fn_trace_gettable(‘c:my_trace.trc’, default)
GO
OR
SELECT * FROM ::fn_trace_gettable((‘c:my_trace.trc’, -1)
GO
HOW TO: Programmatically Load Trace Files into Tables
http://support.microsoft.com/kb/270599 YOu can also use ClearTrace utility… http://weblogs.sqlteam.com/billg/archive/2004/04/11/cleartrace.aspx
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thanks MohammedU. But when i ran SELECT * FROM ::fn_trace_gettable(‘c: race.trc’,default), I am only able to read the data in this trace file. I was wondering, how could i load this into a SQL Server table and run my analysis? Apprecaited some input. Thanks,

---

SELECT * into TableName FROM ::fn_trace_gettable(‘TraceFileName with Path’ ,default) Or you can create a table then use INSERT INTO Tablename …
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thank you MohammedU.

---

I already have the trace table and how can i do load multiple files into this table? I am runing the following statement and system isnt accepting it: insert INTO Trace_table FROM ::fn_trace_gettable(‘c:share racing1.trc’, default)

---

You haven’t SELECT’ed anything to be inserted… If you created your table from SELECT *
INTO Trace_Table
FROM ::fn_trace_gettable(‘c:MyTrace.trc’,default)
Then you would insert into it via INSERT INTO Trace_Table
SELECT *
FROM ::fn_trace_gettable(‘c:MyTrace_02.trc’,default)
However, I do not reccomend you do this as it creates a table that contains a column for EVERY traceable event class column. Not just the events that you requested to be traced. In my experience, it’s better to create your trace tables columns based on the class columns that you are recording (TextData, DatbaseID, ObjectID etc) and insert into them via explicit column lists instead of *.

---

I have 5 trace files generated by my profiler. I loaded the first file sucesfully, but when trying to load the second file, system reports: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table ‘trace1’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
I have tried the following command to by-pass the above message. but no success: set identity_insert trace1 on.
Appreciated if some one please advice.
Thanks,
Cali

---

You have to specify all the columns….
Check BOL topic "SET IDENTITY_INSERT" MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

This would be another problem with loading trace files via SELECT * instead of named columns in the select list. The trace files use an Identity value to keep themseleves in order and you need to account for that when loading trace files. You’re also potentially wasting large quantaties of storage in the database to hold these unneccessarily wide tables.

---

]]>