MORE ABOUT ME

HOW TO: Trace Statements Without SQL Profiler

28 Feb HOW TO: Trace Statements Without SQL Profiler

I came across an interesting scenario last week, someone needed to trace statements and they didn’t have SQL Profiler installed. Turns out they don’t actually need SQL Profiler in order to trace the statements hitting the instance. There is a trace flag (4032) that can be used to trace statements. When used with 3605, you can have all the statements written to the error log for review.

I played around a bit with getting this working and found a few things that were interesting enough for a geek like me. So I made a video, it’s just under 3 minutes long:

If you happen to be one of those persons that needs to do some tracing but don’t have SQL Profiler handy, you may still be able to get the job done.

 

 

One Pingback/Trackback

  • http://sqlskills.com/blogs/jonathan Jonathan Kehayias

    Why can’t they just do a server side trace to a file and then read the file using TSQL with fn_trace_gettable?  That makes a whole lot more sense for this type of thing than using a trace flag to bloat your ERRORLOG file.  Alternatively if they are on SQL Express, there is a free Profiler replacement they could download:
    http://sites.google.com/site/sqlprofiler/

    • ThomasLaRock

      Thanks for the link to the Profiler replacement tool. Not everyone is allowed to download and run tools to their desktops these days, but for those that still have the necessary rights to do so that tool should work nicely.

      And yes, they could run a server side trace and then use TSQL to read from a file. Thanks for presenting a second option. 

    • Cathalobrien

      Thanks for the tool for SQL Express, was gonna try out this method but the free profiler might be better.

    • Alex

      as far as i know sqlprofiler now paid app
      you can try ExpressProfiler from CodePlex – http://expressprofiler.codeplex.com/

  • Vince Gregory

    Any chance of a wmv download of your YouTube video? YouTube is resticted on our company coonection.

  • Jhserres

    good approach! However the drawback is that one must stop and re-start sql service….One cannot do that in PROD! huh?
    One should resort instead to DMOs

  • Pingback: Trace To the Error Log | XL-UAT()