Reading SQL Server Roll Over Trace Files into Table

The default roll over files for SQL Server 2005 Trace do not get imported by the fn_trace_gettable if this roll over is done through the trace UI. To fix this problem do the following.

Create a table to hold the trace table
SELECT * INTO Trace071910
FROM fn_trace_gettable('E:\trace_071910\Monitoring_071910.trc', default);
GO
To insert the first trace file records in to the table

Then insert the other records starting from the roll over file which starts with _1, this would start inserting all the records till the end of the trace roll over file is reached.

INSERT INTO Trace071910
SELECT *
FROM fn_trace_gettable('E:\trace_071910\Monitoring_071910_1.trc', default);
GO

In the event of the duration column not available on the trace file we can update it into the trace table with the following query

UPDATE Trace071910
SET duration = DATEDIFF (ms ,startTime ,endTime)

Here I have converted the duration to a millisecond.