SQL:StatementCompleted trace event?

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  August 19, 2013 at 1:14 am

  #297008

  Hi,

  I have found one of the SQL blogs for tracing SP completed duration as below code

  In this code, How do we include SQL:StatementCompleted trace event?

  CREATE PROCEDURE [StartTrace]

  AS

  BEGIN

  DECLARE @trc INT

  DECLARE @TraceID INT

  DECLARE @maxfilesize BIGINT

  SET @maxfilesize = 100

  DECLARE @file NVARCHAR(500)

  SELECT @file = N'D:\TraceCollection\Test_Trace'

  EXEC @trc = SP_TRACE_CREATE

  @traceid = @TraceID output,

  @options = 2, --TRACE_FILE_ROLLOVER

  @tracefile = @file,

  @maxfilesize = @maxfilesize,

  @stoptime = NULL,

  @filecount = 10000

  -- Set the events

  --Event number 43 is SP:Complete

  --right after 43, we have number like 1, 12, 14 etc. which are column numbers of event

  DECLARE @on BIT, @ret INT

  SET @on = 1

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 1, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 13, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 14, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 15, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 18, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 28, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 34, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 35, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 48, @on

  EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 59, @on

  EXEC SP_TRACE_SETFILTER

  @TraceID,

  28, -- 28 is a column number for "Object Type"

  0, -- and

  0, -- ==

  8272 --we are filtering that only SPs should come and store in trace file

  -- Set the trace status to start

  EXEC sp_trace_setstatus @TraceID, 1

  END

  GO

  Thanks

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  August 19, 2013 at 1:18 am

  #1642563

  By adding the appropriate calls to sp_trace_event. You find the documentation for sp_trace_event in Books Online. The codes for the events and columns are available in sys.trace_events and another DMV of which I never remember the name, but it is in Books Online as well.

  The by far easiest way to set up a server-side trace is to configure the trace in Profiler, and when you are satisfied with the selection, select File->Export from the menu.

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]