• PAH-440118 (12/2/2010)


    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)'.’

    Any Suggestions?

    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:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me