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.
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/
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.
Thanks for the tool for SQL Express, was gonna try out this method but the free profiler might be better.
as far as i know sqlprofiler now paid app
you can try ExpressProfiler from CodePlex – http://expressprofiler.codeplex.com/
Any chance of a wmv download of your YouTube video? YouTube is resticted on our company coonection.
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
http://idealprogrammer.com/net-languages/c/idealsqltracer-simple-free-open-source-alternative-sql-profiler/ – IdealSqlTracer is another free, open source alternative to Sql Profiler. It allows you to quickly grab and format all of the sql that is generated behind any web page or desktop app.
https://uploads.disquscdn.com/images/c485fab766b0c1d619040038faee24c76b1ae887613d4d2a1f93512ffad19d36.png