INF: How to Create a SQL Server 2000 Trace

Article translations Article translations
Article ID: 283790 - 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 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 
				

Properties

Article ID: 283790 - Last Review: November 6, 2003 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbinfo KB283790

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com