"Erland Sommarskog" wrote:

> Fritz Theiss (fr***@anywhere.com) writes:
> > in order to troubleshoot a given scenario i have to find out, which
> > stored procedure, etc. is firing as a specific piece of t-sql code
> > (which has been written by different people). how can i figure this out?
> > i am aware of sql profiler, but in case this is the right tool, what to
> > to do in detail/which event is to monitor and how can i trace only, if
> > the given t-sql statement is fired assuming tons of t-sqls are fired a
> > day?
> 
> You could set up a filter on TextData that includes some text from this
> piece of code. Assuming that you know what code you are looking for
> that is.
>
> It could help if you describe you scenario in little more detail. Is this
> T-SQL code a SELECT or an update action?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

"Erland Sommarskog" wrote:

> Fritz Theiss (FritzThe***@discussions.microsoft.com) writes:
> > thanks fo far.
> >
> > let us say, i would like to see over a long period of time, when i.e. a
> > stored procedure called xyz is crawled. this sp is doing an insert.
> >
> > assuming i do run a profiler trace - what do i have to enable in order to
> > see just this sp used? is the aforementioned filter the only way? i would
> > like to avoid receiving a huge profiler trace costing gb of data....
> >
> > or - if profiler is not the right idea - what to enable instead?
>
> Profiler is definitely wrong. If trace is the answer, you should run it
> server-side. You can still use Profiler to set up the trace, and then
> use Export function (under the File menu). If you have multiple filters
> just check that the filters are export correctly with regards to AND and
> OR. The 2005 Profiler tends to get this wrong.
>
> Whether a server-side trace is the right thing, is hard to tell from
> the meagre information you have provided. The thought of a long-
> running trace with StmtCompleted in it, is not appealing. Even if you
> filter well, generating the events still takes some load.
>
> But I can't really think of any alternatives. You could set up a trigger
> on the table that is being inserted to, but there is no way to retrieve
> the call stack. You can call DBCC INPUTBUFFER ir you arrange the
> permissions, but that may not be sufficient.
>
> Which version of SQL Server are you on?
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>