December 1, 2010 at 10:13 pm
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
December 2, 2010 at 4:25 am
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?
December 2, 2010 at 5:01 am
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
December 2, 2010 at 5:47 am
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.
December 2, 2010 at 6:08 am
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
December 2, 2010 at 6:09 am
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
December 2, 2010 at 6:46 am
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
December 2, 2010 at 7:00 am
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
December 2, 2010 at 7:07 am
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
December 2, 2010 at 7:07 am
What is the command to delete to the trace definition?
December 2, 2010 at 7:08 am
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
December 2, 2010 at 7:15 am
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
December 2, 2010 at 7:17 am
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
December 2, 2010 at 7:26 am
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
December 2, 2010 at 7:41 am
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.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply