Okay. Just went through this several weeks ago. This ended up being my solution. Because of the amount of events that I'm tracking I had to create a stored procedure because the set events went beyond the length of the Jobs window. Here is the header for the stored procedure. First step is to output your trace setup to a SQL Script. Then use the following to modify the filename and size bits.
-------------------------------
CREATE PROCEDURE CP_Start_Trace
AS
IF not exists (SELECT * FROM :: fn_trace_getinfo(default))
-- Create a Queue
-- declare error label
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 25
declare @TrcFileName NVarchar(245)
set @TrcFileName =cast('%path%\%filename%_' as NVarchar) +
cast(right('0000' + cast(datepart(year,getdate()) as varchar),4) as NVarchar) +
cast(right('00' + cast(datepart(month,getdate()) as varchar),2) as NVarchar) +
cast(right('00' + cast(datepart(day,getdate()) as varchar),2) as NVarchar) +
cast(right('00' + cast(datepart(hour,getdate()) as varchar),2) as NVarchar)
exec @rc = sp_trace_create @TraceID output, 2,@TrcFileName , @maxfilesize, NULL
if (@rc != 0) goto error
------------------------------
I'm outputting to disk -- it just seems easier. I use the date and hour in the filename because the trace fails if you try to recycle the file name.
After you have built your SP. In the EM go to SQL Server Group -> %servername% -> Management -> SQL Server Agent -> Jobs. Do a "New Job", Name it, run as SA. Under the Steps add a new step and put in "EXECUTE CP_Start_Trace". Under the schedule set it to start whenever the SQL Server Agent starts.
I'll follow on in another post about deletes.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.