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

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

  • 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

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

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

  • 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 39 total)

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