T-SQL Stored Procedure Creates Trace Tables from Trace Files

I’ve always preferred loading the data from a trace (.trc) file into a table for analysis. For me, it’s easier to sort and handle trace data when it’s in a table because I can easily run queries. So, I wrote the LoadTraceFileToTable stored procedure. With this stored procedure, I can trace whatever I want, put the results in a trace file, use Load- TraceFileToTable to load the data from the trace file into a trace table, run queries against the table, and dispose of the table without losing the original trace file.

The LoadTraceFileToTable stored procedure isn’t meant to replace but rather complement SQL Server Profiler, which also lets you create trace tables from trace files. However, LoadTraceFileToTable does have one advantage over Profiler. With Profiler, the trace table can reach millions of rows during the profiling process and hence enlarge the master or user database. With LoadTraceFileToTable, the table is created after and not during the profiling process, so the database isn’t needlessly enlarged.

The LoadTraceFileToTable stored procedure, which Listing 1 shows, constructs a dynamic T-SQL statement. Specifically, it constructs a SELECT INTO statement that uses the ::fn_trace_gettable system function. This function transforms a trace file into a table. The LoadTraceFile ToTable stored procedure also adds a bigint identity column to sequence the trace rows, as callout A shows.

CREATE PROCEDURE LoadTraceFileToTable 
  (@traceFileName varchar(200), 
  @newTraceTableName sysname)
AS
BEGIN 
  DECLARE @tsqlStmt varchar(400)
  SET NOCOUNT ON
  SET @tsqlStmt = 

-- BEGIN CALLOUT A
  'SELECT IDENTITY(bigInt,1,1) AS Row_Number,' +
  '* ' + ' INTO ' + RTRIM(LTRIM(@newTraceTableName)) +
  ' FROM ::fn_trace_gettable ( ' + '''' + 
  @traceFileName + '''' + ',default)'
-- END CALLOUT A

  PRINT @tsqlStmt
  EXEC (@tsqlStmt)
  SET NOCOUNT OFF
END
GO

I wrote the LoadTraceFileToTable stored procedure for use on SQL Server 2005. To use LoadTraceFileToTable, you need to pass in two parameters. The first parameter is the pathname to the target trace file. The second parameter is the name of the table in which you want to put the trace file’s data. The trace table’s name needs to be in the format database.schema.tablename. For example, if the trace file’s pathname is C:\ELI_TRACE.trc and the trace table’s name is AdventureWorks.dbo .trc_04052007, you’d run the command

EXEC LoadTraceFileToTable
@traceFileName = ‘C:\ELI_TRACE.trc’,
@newTraceTableName =
‘AdventureWorks.dbo.trc_04052007’

Before you run this command, though, you need to make sure that the following conditions are met. (The stored procedure doesn’t check for these conditions.)

The trace file must exist prior to the stored procedure’s execution.
The trace file parameter must be the full pathname.
The table name parameter must be in the format database. schema.table.
The trace table shouldn’t exist prior to the stored procedure’s execution.
The SELECT INTO statement must be a valid statement in the server (i.e., the SELECT INTO/Bulk Copy option must be enabled).

—Eli Leiba, Senior Application DBA,
Israel Electric Company

Comments

Plain text