sp_trace_create
Creates a trace definition. The new trace will be in a stopped state.
Syntax
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
Arguments
[ @traceid = ] trace_id
Is the number assigned by Microsoft® SQL Server™ 2000 to the new trace. Any user-provided input will be ignored. trace_id is int, with a default of NULL. The user employs the trace_id value to identify, modify, and control the trace defined by this stored procedure.
[ @options = ] option_value
Specifies the options set for the trace. option_value is int, with no default. Users may choose a combination of these options by specifying the sum value of options picked. For example, to turn on both the options TRACE_FILE_ROLLOVER and SHUTDOWN_ON_ERROR, specify 6 for option_value.
This table lists the options, descriptions, and their values.
| Option name | Option value | Description |
|---|---|---|
| TRACE_PRODUCE_ROWSET | 1 | Specifies that the trace will produce a rowset. |
| TRACE_FILE_ROLLOVER | 2 | Specifies that when the max_file_size is reached, the current trace file is closed and a new file is created. All new records will be written to the new file. The new file will have the same name as the previous file, but an integer will be appended to indicate its sequence. For example, if the original trace file is named filename.trc, the next trace file is named filename_1.trc, the following trace file is filename_2.trc, and so on.
As more rollover trace files are created, the integer value appended to the file name increases sequentially. SQL Server uses the default value of max_file_size (5 MB) if this option is specified without specifying a value for max_file_size. |
| SHUTDOWN_ON_ERROR | 4 | Specifies that if the trace cannot be written to the file for whatever reason, SQL Server shuts down. This option is useful when performing security audit traces. |
| TRACE_PRODUCE_BLACKBOX | 8 | Specifies that a record of the last 5 MB of trace information produced by the server will be saved by the server. TRACE_PRODUCE_BLACKBOX is incompatible with all other options. |
[ @tracefile = ] 'trace_file'
Specifies the location and file name to which the trace will be written. trace_file is nvarchar (245) with no default. trace_file can be either a local directory (such as N 'C:\MSSQL\Trace\trace.trc') or a UNC to a share or path (N'\\Servername\Sharename\Directory\trace.trc').
SQL Server will append a .trc extension to all trace file names. If the TRACE_FILE_ROLLOVER option and a max_file_size are specified, SQL Server creates a new trace file when the original trace file grows to its maximum size. The new file has the same name as the original file, but _n is appended to indicate its sequence, starting with 1. For example, if the first trace file is named filename.trc, the second trace file is named filename_1.trc.
trace_file cannot be specified when the TRACE_PRODUCE_BLACKBOX option is used.
[ @maxfilesize = ] max_file_size
Specifies the maximum size in megabytes (MB) a trace file can grow. max_file_size is bigint, with a default value of 5.
If this parameter is specified without the TRACE_FILE_ROLLOVER option, the trace stops recording to the file when the disk space used exceeds the amount specified by max_file_size.
[ @stoptime = ] 'stop_time'
Specifies the date and time the trace will be stopped. stop_time is datetime, with a default of NULL. If NULL, the trace will run until it is manually stopped or until the server shuts down.
If both stop_time and max_file_size are specified, and TRACE_FILE_ROLLOVER is not specified, the trace will stop when either the specified stop time or maximum file size is reached. If stop_time, max_file_size, and TRACE_FILE_ROLLOVER are specified, the trace will stop at the specified stop time, assuming the trace does not fill up the drive.
Return Code Values
This table describes the code values that users may get following completion of the stored procedure.
| Return code | Description |
|---|---|
| 0 | No error. |
| 1 | Unknown error. |
| 10 | Invalid options. Returned when options specified are incompatible. |
| 12 | File not created. |
| 13 | Out of memory. Returned when there is not enough memory to perform the specified action. |
| 14 | Invalid stop time. Returned when the stop time specified has already happened. |
| 15 | Invalid parameters. Returned when the user supplied incompatible parameters. |
Remarks
sp_trace_create is a Microsoft SQL Server 2000 stored procedure that performs many of the actions previously executed by xp_trace_* extended stored procedures available in earlier versions of SQL Server. Use sp_trace_create instead of:
- xp_trace_addnewqueue
- xp_trace_setqueuecreateinfo
- xp_trace_setqueuedestination
sp_trace_create only creates a trace definition. This stored procedure cannot be used to start or change a trace.
Parameters of all SQL Trace stored procedures (sp_trace_xx) are strictly typed. If these parameters are not called with the correct input parameter data types, as specified in the argument description, the stored procedure will return an error.
Permissions
Only members of the sysadmin fixed server role can execute sp_trace_create.