Set Up And Schedule a Server Side Trace

Question

Post reply

Set Up And Schedule a Server Side Trace

Grant Fritchey

SSC Guru

Points: 397721
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 1232 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: 134873 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 SSChasing Mays Points: 634 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 184388 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

PAH-440118

Ten Centuries

Points: 1232

December 2, 2010 at 7:07 am

#1257856

What is the command to delete to the trace definition?

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 134873 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: 343 More actions · Answer 14

arturopw

Old Hand

Points: 343

December 2, 2010 at 7:41 am

#1257893

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.