Create SQL Trace

Trace is a lightweight tracking tool that almost doesn't have any side effects on the system, turns on trace, and often helps DBAs track down some very useful information. Trace appears earlier, MS launches the more powerful extended events to replace the trace.

This feature is removed in a future version of Microsoft SQL Server. Avoid using this feature in the new development work, and plan to modify applications the currently use this feature. Use Extended Events instead.

One, use TSQL script to create SQL Trace

Step1, using sp_trace_create to create a trace definition

Sp_trace_create only creates a trace definition. This stored procedure cannot is used to start or change a trace.

Sp_trace_create[@traceid =]trace_id OUTPUT,[@options =]Option_value,[@tracefile =] 'Trace_file'      [, [@maxfilesize =]Max_file_size][, [@stoptime =] 'Stop_time' ]     [, [@filecount =] 'max_rollover_files']

Step2, add event and column for trace using sys.sp_trace_setevent

adds or removes an event or event column to a trace. sp_trace_setevent May is executed only on existing traces that is stopped (stat US is 0 ). An error is returned if the stored procedure is executed on a trace that does not exist or whose Status is not 0 .

[]  trace_id            []  event_id           [  ]  column_id           [ ] on

Step3, use Sys.sp_trace_setfilter to set the filter for trace, or not to set

 sp_trace_setfilter [  @traceid =  Span style= "color: #ff0000;" >]   trace_id,  [  @columnid =    column_id,  
    
     [
      @logical_operator =    Logical_operator,  [  @comparison_ope Rator =    Comparison_operator, [  @value =   value 

STEP4, start or stop trace using Sys.sp_trace_setstatus
Modifies the current state of the specified trace.

[][] status

The available values for the status parameter are:
0: Stops the specified trace.

1: Starts the specified trace.

2: Closes the specified trace and deletes its definition from the server.

Second, use SQL profile to create SQL Trace

SQL profile is a visual tool for creating SQL Trace, and the process of creating a trace is straightforward and straightforward, by contrast, using TSQL script to create SQL Trace is cumbersome. SQL Server profile has a cool feature of being able to export script that creates SQL Trace.

After you create the trace, click File->export->scipt Trace Definition to export the script code for the SQL profile creation trace.

The exported script, which cannot be used directly, must be modified by a code that specifies the file or Table that stores the trace data when the trace is created. That's all.

--Create a QueueDeclare @rc intDeclare @TraceID intDeclare @maxfilesize bigintSet @maxfilesize = 5
-- Client side File and Table cannot be scripted exec @rc =Sp_trace_create@TraceIDOutput0N'Insertfilenamehere',@maxfilesize,NULL if(@rc != 0)GotoError--Set The EventsDeclare @on bitSet @on = 1execSp_trace_setevent@TraceID, -,1,@onexecSp_trace_setevent@TraceID, -,9,@on--Delete many commands here-----Set the FiltersDeclare @intfilter intDeclare @bigintfilter bigintexecSp_trace_setfilter@TraceID,1,0,6N'%drop%'--Set The trace status to startexecSp_trace_setstatus@TraceID,1--Display Trace ID for the future referencesSelectTraceid=@TraceIDGotoFinisherror:SelectErrorCode=@rcFinish:Go

Reference doc:

SQL Server about trace (trace) that little thing

Create SQL Trace