Set Up And Schedule a Server Side Trace

  • 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

  • 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?

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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...

  • 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

  • What is the command to delete to the trace definition?

  • 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

  • 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

  • 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

  • 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

  • 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