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.
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:
The @traceini file must contain the following information:
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.
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.
Change @stoptime to set a time for the trace to automatically stop.
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.
Change @events to control the events traced.
Change @columns to control the columns traced.
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:
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):
The following columns are traced (@columns):
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: