Microsoft KB Archive/283790

= INF: How to Create a SQL Server 2000 Trace =

Article ID: 283790

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q283790



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

Keywords: kbinfo KB283790

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.