August 31, 2009 at 4:37 am
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