added 1705 characters in body
Source Link
Gabe
  • 151
  • 4

If you tell Profiler not to filter out its own queries, you'd probably see it calling these stored procedures:

As described by the documentationthe documentation, you execute the procedures in this order:

  1. Create a trace by using sp_trace_create.
  2. Add events with sp_trace_setevent.
  3. (Optional) Set a filter with sp_trace_setfilter.
  4. Start the trace with sp_trace_setstatus.
  5. Stop the trace with sp_trace_setstatus.
  6. Close the trace with sp_trace_setstatus.

You will end up with a .trc file that you can then open in SQL Profiler or read with fn_trace_gettable.

Here's an example script to start a trace:

declare @rc int
declare @TraceID int

-- create the trace
exec @rc = sp_trace_create @TraceID output, 0, N'trace-filename'

-- set which events to capture
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on

-- start the trace
exec sp_trace_setstatus @TraceID, 1

If you tell Profiler not to filter out its own queries, you'd probably see it calling these stored procedures:

As described by the documentation, you execute the procedures in this order:

  1. Create a trace by using sp_trace_create.
  2. Add events with sp_trace_setevent.
  3. (Optional) Set a filter with sp_trace_setfilter.
  4. Start the trace with sp_trace_setstatus.
  5. Stop the trace with sp_trace_setstatus.
  6. Close the trace with sp_trace_setstatus.

You will end up with a .trc file that you can then open in SQL Profiler.

If you tell Profiler not to filter out its own queries, you'd probably see it calling these stored procedures:

As described by the documentation, you execute the procedures in this order:

  1. Create a trace by using sp_trace_create.
  2. Add events with sp_trace_setevent.
  3. (Optional) Set a filter with sp_trace_setfilter.
  4. Start the trace with sp_trace_setstatus.
  5. Stop the trace with sp_trace_setstatus.
  6. Close the trace with sp_trace_setstatus.

You will end up with a .trc file that you can then open in SQL Profiler or read with fn_trace_gettable.

Here's an example script to start a trace:

declare @rc int
declare @TraceID int

-- create the trace
exec @rc = sp_trace_create @TraceID output, 0, N'trace-filename'

-- set which events to capture
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on

-- start the trace
exec sp_trace_setstatus @TraceID, 1
Source Link
Gabe
  • 151
  • 4

If you tell Profiler not to filter out its own queries, you'd probably see it calling these stored procedures:

As described by the documentation, you execute the procedures in this order:

  1. Create a trace by using sp_trace_create.
  2. Add events with sp_trace_setevent.
  3. (Optional) Set a filter with sp_trace_setfilter.
  4. Start the trace with sp_trace_setstatus.
  5. Stop the trace with sp_trace_setstatus.
  6. Close the trace with sp_trace_setstatus.

You will end up with a .trc file that you can then open in SQL Profiler.