INF: How to Create a SQL Server 2000 Trace

View products that this article applies to.

This article was previously published under Q283790

Expand all | Collapse all

SUMMARY

This article describes how to create and use a stored procedure called trace_build that creates a trace from arguments in a text file. This stored procedure is an alternative to using SQL Profiler to create a script to create a trace.

MORE INFORMATION

The syntax for trace_build is as follows:

trace_build  @traceini

Where @traceini is the name of a text file that contains information for creating a trace. The file name is defined with a datatype of nvarchar(245) and has a default value of 'C:\ActivityTrace.ini'.

The following example creates a trace from information contained in the file C:\Trace1.ini:

trace_build 'c:\Trace1.ini'

The @traceini file must contain the following information:

@tracefile
Data is written to the trace file that is specified in the @tracefile variable. Change @tracefile to save the trace to a different drive and file name. Note that a .trc extension is automatically added by the trace.
@maxfilesize
When the trace file reaches the number of megabytes specified in the @maxfilesize variable, the file is closed. Change @maxfilesize to increase or reduce the size in megabytes of the trace file.
@stoptime
Change @stoptime to set a time for the trace to automatically stop.
@options
When the @options argument is set to 2, the trace will create new trace files when the @maxfilesize variable is exceeded. When the trace file reaches the number of megabytes specified in the @maxfilesize variable, the file is closed and a new file is created. If the original trace file specified in the @tracefile variable is C:\ActivityTrace, the original file is named C:\ActivityTrace.trc. The next trace file is C:\ActivityTrace_1.trc, the next trace file is C:\ActivityTrace_2.trc, and so on.
@events
Change @events to control the events traced.
@columns
Change @columns to control the columns traced.
@filter1
The number of @filter variables can vary from no @filter variable to @filter99. The filter variables must start with @filter1 and continue in sequence to the last filter. The four arguments in the @filter variable are column_id, logical_operator, comparison_operator, and value. The arguments are used by the system stored procedure, sp_trace_setfilter, to create a filter.

For more information about the @tracefile, @maxfilesize, @stoptime, and @options arguments, see the sp_trace_create article in SQL Server 2000 Books Online.

Additional information about the @events and @columns arguments is available in the SQL Server 2000 Books Online article sp_trace_setevent.

Additional information about filters is available in the SQL Server 2000 Books Online article sp_trace_setfilter.

For example, consider the following contents of a @traceini file called C:\TraceArgs.ini:

@tracefile         = C:\ActivityTrace 
@maxfilesize       = 50 
@stoptime          = 2010-12-31 12:00:00.000 
@options           = 2 
@events            = 10,11,12,13,16,17,19,33,42,43,55 
@columns           = 1,2,3,6,9,10,11,12,13,14,15,16,17,18,25 
@filter1           = 10, 0, 7, N'SQL Profiler'

When the following statement is executed:

trace_build 'C:\TraceArgs.ini'

the script creates a tracefile, C:\ActivityTrace.trc (@tracefile), that contains the trace. When the tracefile reaches 50 MB (@maxfilesize), C:\ActivityTrace.trc is closed and a new file, C:\ActivityTrace_1.trc is created (@options). The following events are traced (@events):

RPC:Completed
RPC:Starting
SQL:BatchCompleted
SQL:BatchStarting
Attention
ExistingConnection
DTCTransaction
Exception
SP:Starting
SP:Completed
Hash Warning

The following columns are traced (@columns):

TextData
BinaryData
DatabaseID
NTUserName
ClientProcessID
ApplicationName
SQLSecurityLoginName
SPID
Duration
StartTime
EndTime
Reads
Writes
CPU
IntegerData

The trace filters events so that it only captures events where ApplicationName NOT LIKE 'SQL Profiler' (@filter1).

The trace will stop at 12:00pm on Dec. 31, 2010 (@stoptime).

For additional information on stopping or checking the status of the trace, click the article number below to view the article in the Microsoft Knowledge Base:

283786 (http://support.microsoft.com/kb/283786/EN-US/ ) INF: How to Monitor SQL Server 2000 Traces

To create the trace_build stored procedure, execute the following script in a Query Analyzer window:

CREATE   proc trace_build  @traceini nvarchar (245) = N'C:\ActivityTrace.ini' as 
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
      , @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
      , @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
      , @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint) from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime) from #t1 where left(c1,3) = '@st'
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int) from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist ' + @tracefile + '.trc ' + 'del ' + @tracefile + '*.trc'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime

select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
 begin
 select @event = cast(substring(@events,@estart,@enext-@estart) as int)
 while @cnext > 0
  begin
  select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
  exec sp_trace_setevent @traceid, @event, @column, @on
  select @cstart = @cnext + 1
  select @cnext = charindex(',',@columns,@cstart)
  if @cnext = 0 set @cnext = @lc + 1
  if @cstart >@lc set @cnext = 0
  end
 select @cstart = 1
 select @cnext = charindex(',',@columns,@cstart)
 select @estart = @enext + 1
 select @enext = charindex(',',@events,@estart)
 if @enext = 0 set @enext = @le + 1
 if @estart > @le set @enext = 0
 end
set @cmd1 = 'exec sp_trace_setfilter ' 
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
 from #t1
 where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245)) = N'@filter1'
set @filter_num = 1
while @filter != N'none'
 begin
 exec (@cmd1 + @traceid + ','+@filter)
 set @filter_num = @filter_num + 1
 set @filter = N'none'
 select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
 where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245)) = N'@filter' + cast(@filter_num as nvarchar(3))
 select @filter
 end
finish:
drop table #t1
exec sp_trace_setstatus @traceid, 1