TSQL to create a TraceCollector with a default collection set

Published 24 September 07 03:45 AM | SQL Master 

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;

 

Comments

# Other SQL Server Blogs around the Web said on September 24, 2007 4:27 AM:

use msdb declare @schedule_uid uniqueidentifier select @schedule_uid=(select schedule_uid from msdb.

# SSQA.net - SqlServer-QA.net said on September 24, 2007 4:28 AM:

use msdb declare @schedule_uid uniqueidentifier select @schedule_uid=(select schedule_uid from msdb.

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.