Most people I know, as geeky as some of them are, prefer to create their server-side trace definitions by letting the Profiler GUI do the dirty work. It's so much nicer to just point and click your way to defining a set of trace events, data columns and filters, than having to write dozens or even hundreds of calls to sp_trace_setevent. ( The sp_trace_setevent procedure has to be called once for every combination of event and data column, so for even the smallest trace, with half a dozen events and half a dozen data columns, that would be 36 calls.)
Believe me, I'm not knocking this wonderful capability of having the SQL Server Profiler automatically create my trace definition for me, but whoever updated this mechanism for SQL Server 2005 forgot to check the BOL and see that there is a new parameter to sp_trace_create that wasn't in SQL Server 2000. This parameter does not show up in the automatically generated trace create script, and you have to add it manually if you want it.
Here is the call that I just generated automatically:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
The first parameter is the traceID, which as an output parameter, will be returned, and will then be used in all subsequent calls to sp_trace_* procedures.
The second parameter is a bitmap indicating which of various options are on. 0 means none of the options are enabled. See the BOL for details of the possible values.
The third parameter is, hmm, let me think about it a bit. Oh yeah, it's the full path and file name to save your trace data to. It must be a N'Unicode' string and SQL Server will always add a .trc suffix. So if I called my trace N'C:\mytrace.trc', the name that Explorer would show me would be mytrace.trc.trc. Windows doesn't care, but you might.
The fourth parameter is a variable to indicate the maximum size of any output file. (A value of 5 was assigned to this variable earlier in the script.)
The fifth parameter is the stop time. Since I didn't set a stop time when creating my trace in Profiler, no stop time is listed here, but I can change that if I choose to.
The missing sixth parameter can be found by looking in Books Online for sp_trace_create and seeing this:
[ @filecount =
Specifies the maximum number or trace files to be maintained with the same base filename. max_rollover_files is int, greater than one. This parameter is valid only if the TRACE_FILE_ROLLOVER option is specified. When max_rollover_files is specified, SQL Server tries to maintain no more than max_rollover_files trace files by deleting the oldest trace file before opening a new trace file. SQL Server tracks the age of trace files by appending a number to the base file name.
For example, when the trace_file parameter is specified as "c:\mytrace", a file with the name "c:\mytrace_123.trc" is older than a file with the name "c:\mytrace_124.trc". If max_rollover_files is set to 2, then SQL Server deletes the file "c:\mytrace_123.trc" before creating the trace file "c:\mytrace_125.trc".
Notice that SQL Server only tries to delete each file once, and cannot delete a file that is in use by another process. Therefore, if another application is working with trace files while the trace is running, SQL Server may leave these trace files in the file system.
This parameter is very cool, in part because there is no way to specify a maximum number of files when defining your trace through the GUI. So I guess that's the reason it doesn't appear in the auto-generated script. But it's nice to know that this option is available, as a way to keep large traces on a busy server from eating up all your available disk space.