●トレースの開始DECLARE @RC int, @TraceID int, @on BIT EXEC @rc = sp_trace_create @TraceID output, 0, N'C:\temp\sample_trace' -- Select the return code to see if the trace creation was successful. SELECT RC = @RC, TraceID = @TraceID -- Set the events and data columns you need to capture. SELECT @on = 1 -- 10 is RPC:Completed event. 1 is TextData column. EXEC sp_trace_setevent @TraceID, 10, 1, @on -- 13 is SQL:BatchStarting, 11 is LoginName EXEC sp_trace_setevent @TraceID, 13, 11, @on -- 13 is SQL:BatchStarting, 14 is StartTime EXEC sp_trace_setevent @TraceID, 13, 14, @on -- 12 is SQL:BatchCompleted, 15 is EndTime EXEC sp_trace_setevent @TraceID, 12, 15, @on -- 13 is SQL:BatchStarting, 1 is TextData EXEC sp_trace_setevent @TraceID, 13, 1, @on -- Set any filter. Not provided in this example --EXEC sp_trace_setfilter 1, 10, 0, 6, N'%Profiler%' -- Start Trace (status 1 = start) EXEC @RC = sp_trace_setstatus @TraceID, 1 GO
●トレースの情報表示-- 2はトレースIDselect * from ::fn_trace_getinfo(2)
●停止＋削除DECLARE @TraceID int -- Populate a variable with the trace_id of the current trace SELECT @TraceID = TraceID FROM ::fn_trace_getinfo(default) WHERE VALUE = N'C:\SampleTrace.trc' -- First stop the trace. EXEC sp_trace_setstatus 2, 0 -- Close and then delete its definition from SQL Server. EXEC sp_trace_setstatus 2, 2
●トレースファイルの内容表示select * from fn_trace_gettable('C:\temp\sample_trace.trc',default);go