The trickiest part of setting up a server-side trace comes when you define the events and data columns: You must use their internal ID numbers. Fortunately, the sp_trace_setevent article in SQL Server 2000 Books Online (BOL) that documents the sp_trace_setevent procedure provides the internal ID number for each event and each data column. The code in Listing 1 creates a trace that writes events to the C:\traceselect file. Note that I supplied a filename without a suffix. SQL Server adds the suffix .trc when it creates this file. (If I specified a filename with a suffix—for example, traceselect.trc—SQL Server would create the traceselect.trc.trc file.) If the file exists, you can't create the trace and the call to sp_trace_setstatus returns an error code of 12. The trace in Listing 1's code captures the start time, duration, name, and database of each Statement Completed event (Event ID 41), but the filter restricts the trace so that it captures only events that have SELECT in the name data column.
The parameters of these trace stored procedures must have the correct data type. For example, the filename parameter for sp_trace_create has the nvarchar data type, so you must supply the filename as a Unicode constant with the n prefix. For the sp_trace_setevent procedure, the last argument, which sets data tracing for the event and the columns specified in the second and third parameters to ON or OFF, must have the bit data type. Because you can't use a CAST or CONVERT statement in the procedure call to force a constant of 1 to have the bit data type, I defined a variable to hold the value, and I passed that variable to every call. To modify this trace without stopping and redefining it, you need to pass a bit value of 0 to stop capturing a particular data column. Finally, for the sp_trace_setfilter procedure, you must explicitly pass the character arguments, such as the pattern string N'%select %', as Unicode constants.
Let's take a closer look at the procedure that creates the trace.
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
Note that this trace has two additional parameters that I didn't supply in Listing 1: a maximum file size and a stop time. The stop time is applicable only after the trace has actually started; later in the code in Listing 1, the sp_trace_setstatus procedure starts the trace. The @traceid parameter returns an integer that I must use if I want to modify the trace, stop or restart it, or look at its properties. The code in Listing 1 returns the trace ID's value for informational purposes only, so I'll have the ID if I need to refer to this trace while it runs.
Sp_trace_create's second parameter, @options, lets you specify one or more trace options. Table 2 contains the possible @options values that you can supply to sp_trace_create. A value of 1 tells the trace to produce a rowset and send it to Profiler. You can't use this option value if you capture to a server-side file. Typically, only Profiler-created traces have this option value. Traces that use the sp_trace_create stored procedure should never have an option value of 1, because this value is reserved for Profiler-defined traces.
Because the option_value parameter is a bitmap, you can combine values by adding them together. For example, if you want a trace that enables file rollover and shuts down SQL Server if SQL Server can't write to the trace file, the option value is 6 (4 + 2). Note that you can't combine all option values with all others (e.g., option value 8 by definition doesn't combine with any other option value).
Constantly running some traces can be useful, but be careful about the amount of information you capture. Trace files can grow quite large if they run the entire time that SQL Server runs. To configure a trace to begin automatically every time SQL Server starts, you can put the trace definition into a stored procedure, then use sp_procoption to give that procedure the startup property. Listing 2 illustrates how to create a procedure that defines the trace in Listing 1 and marks that procedure to start automatically.
SQL Server 2000 provides several functions you can use to inspect the status of your traces. The most useful is probably fn_trace_getinfo(), which reports all the parameters that the sp_trace_create procedure specifies, as well as the trace's status (1 = running, 0 = stopped). Here's an example of how to use fn_trace_getinfo():
SELECT * FROM :: fn_trace_getinfo(default)
Prev. page
1
[2]
3
next page 
