How to not collect BinaryData with automated trace?

  • Hi there!

    I've read now many interesting threads about how to script trace definitions and creating automated traces. I tried now for several days with

    /****************************************************/

    /* Created by: SQL Server Profiler 2005 */

    /* Date: 08/31/2009 12:15:26 AM */

    /****************************************************/

    -- Create a Queue

    declare @rc int

    declare @TraceID int

    declare @maxfilesize bigint

    declare @DateTime datetime

    set @DateTime = '2009-09-01 03:30:00.000'

    set @maxfilesize = 4096

    -- Please replace the text InsertFileNameHere, with an appropriate

    -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

    -- will be appended to the filename automatically. If you are writing from

    -- remote server to local drive, please use UNC path and make sure server has

    -- write access to your network share

    exec @rc = sp_trace_create @TraceID output, 0, N'd:\traces\NightTrace', @maxfilesize, @Datetime

    if (@rc != 0) goto error

    -- Client side File and Table cannot be scripted

    -- Writing to a table is not supported through the SP's

    -- Set the events

    declare @on bit

    set @on = 1

    exec sp_trace_setevent @TraceID, 10, 15, @on

    exec sp_trace_setevent @TraceID, 10, 16, @on

    exec sp_trace_setevent @TraceID, 10, 1, @on

    exec sp_trace_setevent @TraceID, 10, 17, @on

    exec sp_trace_setevent @TraceID, 10, 18, @on

    exec sp_trace_setevent @TraceID, 10, 3, @on

    exec sp_trace_setevent @TraceID, 10, 35, @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, 12, 15, @on

    exec sp_trace_setevent @TraceID, 12, 16, @on

    exec sp_trace_setevent @TraceID, 12, 1, @on

    exec sp_trace_setevent @TraceID, 12, 17, @on

    exec sp_trace_setevent @TraceID, 12, 14, @on

    exec sp_trace_setevent @TraceID, 12, 18, @on

    exec sp_trace_setevent @TraceID, 12, 3, @on

    exec sp_trace_setevent @TraceID, 12, 35, @on

    exec sp_trace_setevent @TraceID, 12, 12, @on

    exec sp_trace_setevent @TraceID, 12, 13, @on

    -- Set the Filters

    declare @intfilter int

    declare @bigintfilter bigint

    -- Set the trace status to start

    --exec sp_trace_setstatus @TraceID, 1

    -- display trace id for future references

    select TraceID=@TraceID

    goto finish

    error:

    select ErrorCode=@rc

    finish:

    go

    sp_trace_setevent never enables column 2, BinaryData. Above script was scripted with profiler UI. I also tried with selfwritten scripts containing

    declare @off bit

    set @off = 0

    exec sp_trace_setevent @TraceID, 10, 2, @off

    exec sp_trace_setevent @TraceID, 12, 2, @off

    Still, the trace file allways contains the Binarydata which leads to a huge amount of data... I filled 512MB in 3 minutes. And I don't need the BinaryData. I try to run a trace over one hour in the night to measure base load of my database server, so I really only need above enabled events.

    I also tried

    exec sp_trace_setevent @TraceID, 10, NULL, @on

    exec sp_trace_setevent @TraceID, 12, NULL, @on

    as first two setevent calls, to first enable the event and "clear all columns" as how BOL tells me, and then enabling each needed column. Still BinaryData is in.

    Anybody has an idea about how to disable collection of BinaryData?

    Cheers, ChrisTar

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply