Set Up And Schedule a Server Side Trace

Question

Set Up And Schedule a Server Side Trace

Grant Fritchey

SSC Guru

Points: 398909
More actions
December 1, 2010 at 10:13 pm

#231771

Comments posted to this topic are about the item Set Up And Schedule a Server Side Trace
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

PAH-440118 Ten Centuries Points: 1292 More actions · Answer 1

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?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

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

jason.spangler Say Hey Kid Points: 684 More actions · Answer 3

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.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

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?

Johan seems to have already answered your question for you. Let me know if you have any more.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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.

Thanks.

You know that's not a problem I've run into. I'll have to check on it. Thanks for pointing it out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 6

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Great article again Grant. I just prefer to use sys.traces over fn_trace_getinfo. I think it gives an easier to decipher set of information.

Hmm, I'm going to have to play with the stop time because I thought it deleted the trace definition...

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

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

Writing to file is much, much faster than writing to a database, so yes, it does make a difference. Also, is that database on the same server you're monitoring? That can be a problem, adding overhead where none should exist.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

PAH-440118 Ten Centuries Points: 1292 More actions · Answer 9

What is the command to delete to the trace definition?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 10

Jack Corbett (12/2/2010)
Great article again Grant. I just prefer to use sys.traces over fn_trace_getinfo. I think it gives an easier to decipher set of information.
Hmm, I'm going to have to play with the stop time because I thought it deleted the trace definition...

I did too. If you get to it before I do (at work, can't play at the moment) please post it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 11

PAH-440118 (12/2/2010)
What is the command to delete to the trace definition?

Right here in the Books Online:

1.Execute sp_trace_setstatus by specifying @status = 0 to stop the trace.
2.Execute sp_trace_setstatus by specifying @status = 2 to close the trace and delete its information from the server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 12

Grant Fritchey (12/2/2010)
Writing to file is much, much faster than writing to a database, so yes, it does make a difference. Also, is that database on the same server you're monitoring? That can be a problem, adding overhead where none should exist.

But of course it's on the same server 🙂 Since I'm going to change process around anyway, I'll change it to create the flat files and import then into the database afterwards. I was just being lazy to save a step as it hasn't yet caused a problem. I certainly don't want to be the cause of problems, so thank-you for your response.

Cindy

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 13

I haven't noticed it would not cleanup the trace def in systraces after the end time.

Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing 😉

So in my instances they get cleaned up.

edited

You can find my setup script in the attachement it was to long to just post in the reply :blink:

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

arturopw Old Hand Points: 373 More actions · Answer 14

I just want to say Thanks for this article. When setting up server side trace scripts last week (for the first time) I was annoyed at how cumbersome and error-prone creating the script was. Learning that it can be scripted out from Profiler will save me lots of time and pain next time.