Post reply

Default trace - A Beginner's Guide

  • November 11, 2008 at 12:19 am

    #215096

    Comments posted to this topic are about the item Default trace - A Beginner's Guide


    My blog: http://jahaines.blogspot.com

  • November 11, 2008 at 1:19 am

    #896775

    I like it, but what's the cost of running this on a production server?

    Measure twice, cut once

  • November 11, 2008 at 2:58 am

    #896824

    Nice one ...

  • November 11, 2008 at 4:33 am

    #896855

    Adam,

    Well done! I think the default trace enabled by default? I checked my installations and it's enabled on all of them.

    Mark

  • November 11, 2008 at 6:44 am

    #896910

    Dee (11/11/2008)

    I like it, but what's the cost of running this on a production server?

    I cant really say what the exact cost is because the cost is dependent on multiple factors including usage. I will say that the trace is very light weight. In fact, most do not even know this guy is running in the background.


    My blog: http://jahaines.blogspot.com

  • November 11, 2008 at 6:48 am

    #896913

    Mark Horninger (11/11/2008)

    Adam,

    Well done! Is the default trace enabled by default?

    Mark

    Yes, you have to tell SQL to explicitly turn off this feature by issuing the sp_configure command.


    My blog: http://jahaines.blogspot.com

  • November 11, 2008 at 7:17 am

    #896938

    Adam,

    Nice article. According to BOL you should use sys.traces instead of fn_trace_getinfo. Here is the BOL quote:

    The sys.traces catalog view contains the current running traces on the system. This view is intended as a replacement for the fn_trace_getinfo function.

    Also if you disable this trace many of the reports available in SSMS will no longer work as they get much of their information from this trace.

    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

  • November 11, 2008 at 7:30 am

    #896947

    Jack,

    Thanks for the BOL quote. I find it somewhat amusing that MS states that sys.traces should be used instead of fn_get_trace_info on the sys.traces page; however, on the fn_trace_getinfo page makes absolutely no reference to sys.traces (even in the 2008 documentation).

    Here is a link to the 2008 documentation

    http://msdn.microsoft.com/en-us/library/ms173875.aspx

    -Adam


    My blog: http://jahaines.blogspot.com

  • November 11, 2008 at 7:51 am

    #896955

    That is pretty funny.

    sys.traces has more information and I find it to be in a more usable format than fn_trace_getinfo.

    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

  • November 11, 2008 at 10:12 am

    #897051

    For those of us working in "locked down" environments you will need to have 'ALTER TRACE' permission in order to execute fn_trace_getinfo.

    Most likely this is the permission you would need for sys.traces, which was mentioned in a reply here, as well.

  • November 11, 2008 at 10:22 am

    #897059

    I found the default trace a long while back while wondering around in SQL Install files. As soon as I noticed it I had the questions like performance, who set it and such. So I went digging around and was quite pleased with this little trick of Microsoft's :D. It has saved me more the once in various environment when someone asked when something was modified. But what I wanted to know was Adam is there a way to add events to Default Trace? As you said and most probably noticed it doesn't contain all the event. And adding all the events probably is a bad idea because it will grow the log files; but selected few events. Also how can I make it so it saves more then 5 roll-over files?

    Thanks.

    - Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • November 11, 2008 at 2:03 pm

    #897154

    Good one

  • November 11, 2008 at 2:10 pm

    #897161

    Mohit (11/11/2008)

    But what I wanted to know was Adam is there a way to add events to Default Trace? Also how can I make it so it saves more then 5 roll-over files?

    As far as I know, you can't change the default trace's definition. The events, columns and number of files is hardcoded somewhere. If you want more events or more files, disable the default trace and set up your own one.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2008 at 2:43 pm

    #897176

    Thanks Gail.

    That what I ended up doing; just wanted to make sure I wasn't missing something :).

    - Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • November 11, 2008 at 2:48 pm

    #897179

    Adam,

    GREAT WORK!! Very concise and informative. Thanks!

Viewing 15 posts - 1 through 15 (of 59 total)

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