Thanks for this article. it was just what I needed and a feature I did not know about. I have been experimenting and it looks great however I get an error if the trace file already exists as a new one cannot be created (as you would expect). Is there a way of setting the output file name to be something that changes on each execution?
I have tried to create a variable for the file name and concatenating to this the date but this does not seem to work either.
Declare @SaveTo varchar(max)
Set @Saveto = 'C:\DirName\'+ (cast(Getdate()as char))
exec @rc = sp_trace_create @TraceID output, 2, @Saveto, @maxfilesize, @stoptime,NULL
I get an error of ‘Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.’
You didn't pay attention when declaring your variable as a varchar(max).
Consider the implications for the engine.
the errormessage clearly states it expects a Nvarchar(256) , the easiest way is to actually do just that.
Declare @SaveTo Nvarchar(256)
This is the code I use to get easy to read trace file names:
set @TraceFileName = 'C:\temp\ALZDBA_SQL_Trace'
+ '_' + replace(@@servername,'\','_') + '_'
+ replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') + '_'
-- + '.trc' is being added automatically
Another nice and useful article by Grant ! :w00t:
I also recommend reading Kalen Delaneys findings at http://sqlblog.com/blogs/kalen_delaney/archive/2007/06/29/did-you-know-a-6th-parameter-when-creating-a-trace.aspx where she states there is a parameter to take control on the number of rollover files.
I just learned about this at SQLPass2010 :hehe: