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?

set @TraceFileName = 'C:\temp\ALZDBA_SQL_Trace' 
 + '_' + replace(@@servername,'\','_') + '_' 
+ replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') + '_' 
-- + '.trc' is being added automatically

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?

jason.spangler (12/2/2010)

---

Great article. I have been using this technique for a few months now and it saves tons of resources on the server, plus helps with my sleep when I need to trace a problem at 3am.

One thing to note though, when you run the script, it adds the trace. The trace will stop at the pre-defined stop time, but the trace definition will stay on the server. If you have this as a scheduled job to run every night or every day, it will keep adding new trace definitions. In my jobs, I just create a step to delete the trace after it completes.

CavyPrincess (12/2/2010)

---

Thanks for the great article. I create a baseline performance measure every so often on my servers, and this will simplify the process. I'm currently placing the output directly into the database via an ODBC connection. I'm sure I can change the script samples to do this, but just wondering if I should be doing this. Does it matter?

Thanks,
Cindy