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