TSQL to create a TraceCollector with a default collection set
use msdb
declare @schedule_uid uniqueidentifier
select @schedule_uid=(select schedule_uid from msdb..sysschedules where name=N'CollectorSchedule_Every_15min')
declare @collection_set_id int;
exec dbo.sp_syscollector_create_collection_set
@name = N'SqlTraceDefault',
@schedule_uid = @schedule_uid, -- 15 minutes
@collection_mode = 1, -- non-cached: scheduled
@logging_level = 0, -- minimum logging
@days_until_expiration = 5,
@description = N'This is a demo collection set',
@collection_set_id = @collection_set_id output;
-- ITEMs: Default trace definition
declare @trace_definition xml;
declare @collection_item_id int;
select @trace_definition = convert(xml,
N'<SqlTraceCollector use_default="1">
<Events />
<Filters />
</SqlTraceCollector>');
Once the above collection set is defined you can execute the trace collection procedure to collect the same:
exec dbo.sp_syscollector_create_collection_item
@collection_set_id = @collection_set_id,
@collector_type_uid = N'0E218CF8-ECB5-417B-B533-D851C0251271',
@name = 'SqlTraceDefault',
@frequency = 30,
@parameters = @trace_definition,
@collection_item_id = @collection_item_id output;