Set Up And Schedule a Server Side Trace

  • CavyPrincess (12/2/2010)


    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

    Of course. Happy to help. Also, tracking down Brad McGehee's excellent book will help even 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ALZDBA (12/2/2010)


    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:

    I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.

  • Jack Corbett (12/2/2010)


    ALZDBA (12/2/2010)


    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:

    I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.

    Ha! I was just sitting down to start testing this when I saw your post. Thanks. It's what I thought, but it never hurts to be sure.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Great Stuff Grant.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good stuff as always, Grant! Thanks for taking the time to write articles like this. This one should be in every DBA's quick reference guide. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (12/2/2010)


    Good stuff as always, Grant! Thanks for taking the time to write articles like this. This one should be in every DBA's quick reference guide. 🙂

    Thanks Jeff, but you & both know after you've done it once or twice, it's hard to forget. Hopefully it helps new people get a leg up a little quicker.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • CirquedeSQLeil (12/2/2010)


    Great Stuff Grant.

    Thanks Jason.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.

    Thanks to the peoples that did the testing!

  • jason.spangler (12/3/2010)


    I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.

    Thanks to the peoples that did the testing!

    No worries. That's what SSC is all about , testing.

  • Jack Corbett (12/3/2010)


    jason.spangler (12/3/2010)


    I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.

    Thanks to the peoples that did the testing!

    No worries. That's what SSC is all about , testing.

    Oh indeed ...

    Don't just take anything for granted.

    Test it - Test IT !

    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

  • ALZDBA (12/3/2010)


    Jack Corbett (12/3/2010)


    jason.spangler (12/3/2010)


    I stand corrected everyone. Sorry about the incorrect information. I was told in a class that it had to be cleaned up, and never really tested it. It's great to know that it will auto cleanup.

    Thanks to the peoples that did the testing!

    No worries. That's what SSC is all about , testing.

    Oh indeed ...

    Don't just take anything for granted.

    Test it - Test IT !

    Absolute 100% Agreement!

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • great article, thanks.

    what tweaks to this process are required to run a server-side trace on analysis services? when i export my Script Trace Definition, the 'For SQL...' options are greyed out. i am only able to export 'For Analysis Services...' which produces an xmla file.

    what do i do with that xmla file to schedule the trace?

    my task at hand is that i have to create an audit of access to a cube. any thoughts on whether this is a sufficiently robust mechanism to do this? if not, can anyone suggest alternatives?

    thanks

    rob

  • Hello,

    Thanks for a great article.

    I have a very intermittent problem, occurs only two or three times a month. Would there be any issues with running a server side trace all day, other than creating a lot of .trc files? I'm especially concerned about an all-day trace using too many resources and affecting server performance.

  • Here is the another code snippest to write Server Side Trace

    http://abhijitmore.wordpress.com/2010/03/11/creating-custom-trace/

    Abhijit - http://abhijitmore.wordpress.com

  • rob mcnicol (1/31/2011)


    great article, thanks.

    what tweaks to this process are required to run a server-side trace on analysis services? when i export my Script Trace Definition, the 'For SQL...' options are greyed out. i am only able to export 'For Analysis Services...' which produces an xmla file.

    what do i do with that xmla file to schedule the trace?

    my task at hand is that i have to create an audit of access to a cube. any thoughts on whether this is a sufficiently robust mechanism to do this? if not, can anyone suggest alternatives?

    thanks

    rob

    I am frankly unsure. I've never run a trace on SSAS, so I don't know exacty how it works.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 16 through 30 (of 39 total)

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