Default trace - A Beginner's Guide

  • Adam Haines

    SSC-Insane

    Points: 23197

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

  • ekoner

    Mr or Mrs. 500

    Points: 551

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

    Measure twice, cut once

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice one ...

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Adam,

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

    Mark

  • Adam Haines

    SSC-Insane

    Points: 23197

    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.

  • Adam Haines

    SSC-Insane

    Points: 23197

    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.

  • Jack Corbett

    SSC Guru

    Points: 184372

    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

  • Adam Haines

    SSC-Insane

    Points: 23197

    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

  • Jack Corbett

    SSC Guru

    Points: 184372

    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

  • Adam Gojdas

    SSCommitted

    Points: 1766

    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.

  • Mohit K. Gupta

    SSChampion

    Points: 12130

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

  • dparmender

    SSC Enthusiast

    Points: 159

    Good one

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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
  • Mohit K. Gupta

    SSChampion

    Points: 12130

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

  • David Dye

    SSC Veteran

    Points: 273

    Adam,

    GREAT WORK!! Very concise and informative. Thanks!

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

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