sp_trace_create (Transact-SQL)sp_trace_create (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

建立追蹤定義。Creates a trace definition. 新追蹤會處於已停止狀態。The new trace will be in a stopped state.

重要

這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 請改用擴充事件。Use Extended Events instead.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
sp_trace_create [ @traceid = ] trace_id OUTPUT   
          , [ @options = ] option_value   
          , [ @tracefile = ] 'trace_file'   
     [ , [ @maxfilesize = ] max_file_size ]  
     [ , [ @stoptime = ] 'stop_time' ]  
     [ , [ @filecount = ] 'max_rollover_files' ]  

引數Arguments

[ @traceid = ] trace_id 已指派的數字MicrosoftMicrosoft[SQL Server]SQL Server給新追蹤。[ @traceid = ] trace_id Is the number assigned by MicrosoftMicrosoft [SQL Server]SQL Server to the new trace. 系統會忽略任何使用者所提供的輸入。Any user-provided input will be ignored. trace_idint,預設值是 NULL。trace_id is int, with a default of NULL. 使用者會利用trace_id值來識別、 修改和控制這個預存程序所定義的追蹤。The user employs the trace_id value to identify, modify, and control the trace defined by this stored procedure.

[ @options = ] option_value 指定追蹤所設定的選項。[ @options = ] option_value Specifies the options set for the trace. option_valueint,沒有預設值。option_value is int, with no default. 使用者可以指定所取用選項的總值,來選擇這些選項的組合。Users may choose a combination of these options by specifying the sum value of options picked. 例如,若要開啟這兩個 TRACE_FILE_ROLLOVER 和 SHUTDOWN_ON_ERROR 選項,指定6 for option_valueFor example, to turn on both the options TRACE_FILE_ROLLOVER and SHUTDOWN_ON_ERROR, specify 6 for option_value.

下表列出選項、描述及其值。The following table lists the options, descriptions, and their values.

選項名稱Option name 選項值Option value 描述Description
TRACE_FILE_ROLLOVERTRACE_FILE_ROLLOVER 22 指定當max_file_size為止,目前的追蹤檔案已關閉,且在建立新的檔案。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. 例如,如果原始追蹤檔的名稱是 filename.trc,下一個追蹤檔的名稱就是 filename_1.trc,再下一個追蹤檔的名稱就是 filename_2.trc,依此類推。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 使用的預設值max_file_size (5 MB) 如果未指定的值指定此選項max_file_sizeSQL 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_ERRORSHUTDOWN_ON_ERROR 44 指定追蹤因故無法寫入檔案時,SQL Server 便關機。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_BLACKBOXTRACE_PRODUCE_BLACKBOX 88 指定伺服器將所產生最後 5 MB 的追蹤資訊記錄儲存起來。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 與所有其他選項不相容。TRACE_PRODUCE_BLACKBOX is incompatible with all other options.

[ @tracefile = ] 'trace_file' 指定將寫入追蹤的檔案名稱與位置。[ @tracefile = ] 'trace_file' Specifies the location and file name to which the trace will be written. trace_filenvarchar(245) 沒有預設值。trace_file is nvarchar(245) with no default. trace_file可以是本機目錄 (如 N 'C:\MSSQL\Trace\trace.trc') 或共用或路徑的 UNC (N'\\Servername\Sharename\ Directory\trace.trc')。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]SQL Server 將會附加 .trc延伸至所有的追蹤檔名稱。will append a .trc extension to all trace file names. 如果了 TRACE_FILE_ROLLOVER 選項和max_file_size都有指定,[SQL Server]SQL Server原始追蹤檔成長到其大小上限時,會建立新的追蹤檔案。If the TRACE_FILE_ROLLOVER option and a max_file_size are specified, [SQL Server]SQL Server creates a new trace file when the original trace file grows to its maximum size. 新的檔案具有相同名稱做為原始的檔案,但 __n附加至指出其順序,開頭1The new file has the same name as the original file, but _n is appended to indicate its sequence, starting with 1. 例如,如果第一個追蹤檔案名為filename.trc,第二個追蹤檔案會命名為filename_1.trcFor example, if the first trace file is named filename.trc, the second trace file is named filename_1.trc.

如果使用了 TRACE_FILE_ROLLOVER 選項,建議您不要在原始追蹤檔名稱中使用底線字元。If you use the TRACE_FILE_ROLLOVER option, we recommend that you do not use underscore characters in the original trace file name. 若您使用底線字元,便會發生下列行為:If you do use underscores, the following behavior occurs:

  • SQL Server ProfilerSQL Server Profiler 不會自動載入或提示您載入換用檔案 (如果已經設定任何這些檔案換用選項)。does not automatically load or prompt you to load the rollover files (if either of these file rollover options are configured).

  • Fn_trace_gettable 函數不會載入換用檔案 (當指定利用number_files&lt引數) 的原始的檔案名稱以底線加一個數字值的結束位置。The fn_trace_gettable function does not load rollover files (when specified by using the number_files argument) where the original file name ends with an underscore and a numeric value. (若為檔案換用時自動附加的底線及數值,則不在此列)。(This does not apply to the underscore and number that are automatically appended when a file rolls over.)

注意

為了因應以上兩種行為,您可以重新命名檔案,移除原始檔案名稱中的底線。As a workaround for both of these behaviors, you can rename the files to remove the underscores in the original file name. 例如,如果原始的檔案命名為my_trace.trc,和名為換用檔案my_trace_1.trc,您可以重新命名的檔案mytrace.trcmytrace_1.trc開啟中的檔案之前SQL Server ProfilerSQL Server ProfilerFor example, if the original file is named my_trace.trc, and the rollover file is named my_trace_1.trc, you can rename the files to mytrace.trc and mytrace_1.trc before you open the files in SQL Server ProfilerSQL Server Profiler.

trace_file無法使用 TRACE_PRODUCE_BLACKBOX 選項時指定。trace_file cannot be specified when the TRACE_PRODUCE_BLACKBOX option is used.

[ @maxfilesize = ] max_file_size 指定可以成長的大小上限 (單位為 MB) 的追蹤檔案。[ @maxfilesize = ] max_file_size Specifies the maximum size in megabytes (MB) a trace file can grow. 您將 max_file_sizebigint,預設值是5max_file_size is bigint, with a default value of 5.

如果不使用了 TRACE_FILE_ROLLOVER 選項指定這個參數,則追蹤會停止記錄到檔案時使用的磁碟空間超過指定的數量max_file_sizeIf 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' 指定的日期和時間將會停止追蹤。[ @stoptime = ] 'stop_time' Specifies the date and time the trace will be stopped. stop_timedatetime,預設值是 NULL。stop_time is datetime, with a default of NULL. 如果是 NULL,追蹤就會執行到手動停止或伺服器關機為止。If NULL, the trace runs until it is manually stopped or until the server shuts down.

如果兩個stop_timemax_file_size都有指定,和 TRACE_FILE_ROLLOVER 不指定,追蹤多時指定的停止時間 」 或 「 最大檔案大小上限。If both stop_time and max_file_size are specified, and TRACE_FILE_ROLLOVER is not specified, the trace tops when either the specified stop time or maximum file size is reached. 如果stop_timemax_file_size,而且指定了 TRACE_FILE_ROLLOVER,追蹤就會停止指定的停駐點時,假設追蹤不會填滿磁碟機。If stop_time, max_file_size, and TRACE_FILE_ROLLOVER are specified, the trace stops at the specified stop time, assuming the trace does not fill up the drive.

[ @filecount = ] 'max_rollover_files' 指定的最大的數字或追蹤檔,才能維持相同的基底檔案名稱。[ @filecount = ] 'max_rollover_files' Specifies the maximum number or trace files to be maintained with the same base filename. max_rollover_filesint,大於 1。max_rollover_files is int, greater than one. 只有在指定了 TRACE_FILE_ROLLOVER 選項時,這個參數才有效。This parameter is valid only if the TRACE_FILE_ROLLOVER option is specified. max_rollover_files指定,則[SQL Server]SQL Server嘗試維持不超過max_rollover_files追蹤檔的開啟新的追蹤檔之前,請先刪除最舊的追蹤檔案。When max_rollover_files is specified, [SQL Server]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]SQL Server 會在基礎檔案名稱上附加數字來追蹤這些追蹤檔的存在時間。tracks the age of trace files by appending a number to the base file name.

例如,當trace_file參數指定為"c:\mytrace"時,名稱為"c:\mytrace_123.trc"的檔案為早於具有名稱"c:\mytrace_124.trc"的檔案。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". 如果max_rollover_files是設定為 2,則 SQL Server 刪除"c:\mytrace_123.trc"的檔案再建立追蹤檔"c:\mytrace_125.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".

請注意,[SQL Server]SQL Server 只會嘗試刪除各個檔案一次,且無法刪除有其他程序在使用的檔案。Notice that [SQL Server]SQL Server only tries to delete each file once, and cannot delete a file that is in use by another process. 因此,如果您在進行追蹤時,有另一個應用程式在使用追蹤檔,[SQL Server]SQL Server 會將這些追蹤檔保留在檔案系統中。Therefore, if another application is working with trace files while the trace is running, [SQL Server]SQL Server may leave these trace files in the file system.

傳回碼值Return Code Values

下表描述在預存程序完成之後,使用者可能得到的代碼值。The following table describes the code values that users may get following completion of the stored procedure.

傳回碼Return code 描述Description
00 沒有錯誤。No error.
11 未知的錯誤。Unknown error.
1010 無效的選項。Invalid options. 當指定的選項不相容時,便傳回這個代碼。Returned when options specified are incompatible.
1212 未建立檔案。File not created.
1313 記憶體用完。Out of memory. 當沒有足夠的記憶體可以執行指定的動作時,便傳回這個代碼。Returned when there is not enough memory to perform the specified action.
1414 無效停止時間。Invalid stop time. 當指定的停止時間已發生過時,便傳回這個代碼。Returned when the stop time specified has already happened.
1515 無效的參數。Invalid parameters. 當使用者提供不相容的參數時,便傳回這個代碼。Returned when the user supplied incompatible parameters.

備註Remarks

sp_trace_create[SQL Server]SQL Server預存程序會執行許多先前執行的動作xp_trace_* 擴充預存程序可在舊版的 SQL Server。sp_trace_create is a [SQL Server]SQL Server stored procedure that performs many of the actions previously executed by xp_trace_* extended stored procedures available in earlier versions of SQL Server. 使用sp_trace_create而不是:Use sp_trace_create instead of:

  • xp_trace_addnewqueuexp_trace_addnewqueue

  • xp_trace_setqueuecreateinfoxp_trace_setqueuecreateinfo

  • xp_trace_setqueuedestinationxp_trace_setqueuedestination

sp_trace_create只會建立追蹤定義。sp_trace_create only creates a trace definition. 您無法利用這個預存程序來啟動或變更追蹤。This stored procedure cannot be used to start or change a trace.

參數的所有 SQL 追蹤預存程序 (sp_trace_xx) 都有強制類型。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.

針對sp_trace_create,則[SQL Server]SQL Server服務帳戶必須具有寫入權限的追蹤檔案資料夾。For sp_trace_create, the [SQL Server]SQL Server service account must have Write permission on the trace file folder. 如果 [SQL Server]SQL Server 服務帳戶不是追蹤檔所在電腦的管理員,您就必須將寫入權限明確授與 [SQL Server]SQL Server 服務帳戶。If the [SQL Server]SQL Server service account is not an administrator on the computer where the trace file is located, you must explicitly grant Write permission to the [SQL Server]SQL Server service account.

注意

您可以自動載入追蹤檔案以建立sp_trace_create使用資料表fn_trace_gettable系統函數。You can automatically load the trace file created with sp_trace_create into a table by using the fn_trace_gettable system function. 如需如何使用這個系統函數的詳細資訊,請參閱sys.fn_trace_gettable (TRANSACT-SQL)For information about how to use this system function, see sys.fn_trace_gettable (Transact-SQL).

如需使用追蹤預存程序的範例,請參閱建立追蹤 (Transact-SQL)For an example of using trace stored procedures, see Create a Trace (Transact-SQL).

TRACE_PRODUCE_BLACKBOX具有下列特性:TRACE_PRODUCE_BLACKBOX has the following characteristics:

  • 這是換用追蹤。It is a rollover trace. 預設值file_count為 2,但您可以覆寫使用者利用filecount選項。The default file_count is 2 but can be overridden by the user using filecount option.

  • 預設值file_size一樣與其他追蹤是 5 MB,且可以變更。The default file_size as with other traces is 5 MB and can be changed.

  • 不可指定任何檔名。No filename can be specified. 檔案會儲存為:N'%SQLDIR%\MSSQL\DATA\blackbox.trc'The file will be saved as: N'%SQLDIR%\MSSQL\DATA\blackbox.trc'

  • 追蹤中只會包含下列事件及其資料行:Only the following events and their columns are contained in the trace:

    • RPC 正在開始RPC starting

    • 啟動批次Batch starting

    • ExceptionException

    • 注意Attention

  • 無法從這項追蹤加入或移除事件或資料行。Events or columns cannot be added or removed from this trace.

  • 無法針對這項追蹤指定篩選器。Filters cannot be specified for this trace.

PermissionsPermissions

使用者必須有 ALTER TRACE 權限。User must have ALTER TRACE permission.

另請參閱See Also

sp_trace_generateevent (Transact-SQL) sp_trace_generateevent (Transact-SQL)
sp_trace_setevent (Transact-SQL) sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL) sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL) sp_trace_setstatus (Transact-SQL)
SQL 追蹤SQL Trace