Re: setting up a trace through extended stored procedures - Mailing list pgsql-general

Brian,

I was excited to see your response and immediately went to SQL Profiler to
poke around. I didn't see any option to set the file size (using version
7.0), but did find a way to limit the number of rows to be displayed to the
console. I clicked on Tools, Options and then clicked on the Display tab and
was able to set the value in front of the text saying "Number of lines of
text data displayed in the window".

After removing the SQL Profiler from the filter and starting the trace
however, I unfortunately did not see any calls made to limit the number of
events/transactions to track. What am I missing? I only saw a series of the
following statement with different input parameter values, defining
different events to trace.

declare @P1 int
declare @P2 int
declare @P3 int
set @P1=5
set @P2=10
set @P3=1
exec xp_trace_seteventclassrequired @P1, @P2, @P3

Thanks for your help so far.

BTW: I enjoy your articles from SQL Server magazine. It was a pleasant
surprise to see your name here as well.

Adam



"Brian Moran" <brianm@crosstier.com> wrote in message
news:#sQLpfsvBHA.2468@tkmsftngp04...
> I don't have Books Online in front of me right now, and don't remember the
> syntax, but... you can set maximum sizes for a trace file. You can set
this
> up from the Profiler GUI and then save the trace definition to see the
> correct syntax. I know this isn't a lot of detail, but I think it should
> help you. I'll try to post more detail when I get to work and have BOL
> handy...
>
> --
> Brian Moran
> SQL Server MVP
>
>
> "Adam" <nospam@nospam.com> wrote in message
> news:Lkhe8.104765$s43.24721268@typhoon.columbus.rr.com...
> > This question is for the hardcore SQL Server gurus out there.
> >
> > In summary, the question is pertaining to setting up a custom trace
using
> > extended stored procedures in production. The idea is to find the source
> of
> > a deadlock that occurs on occasional basis. My question deals mainly
with
> > the following stored procedures.
> > xp_trace_setqueryhistory
> > xp_trace_flushqueryhistory
> > xp_trace_addnewqueue
> >
> >
> > Here is the detail: Every other day or so, the insert to this one table
> > fails in production environment. Unfortunately, the error handling code
> > doesn't trap the error number so I am not sure what error is occurring
and
> > causing the insert to fail. I have tried and have been unsuccessful in
> > reproducing the problem in the development environment.
> >
> > I am guessing that it is due to a dead lock and would like to find the
> > source of the problem through SQL Profiler. Since I am not at the site,
I
> > can't just fire up SQL Profiler and try to trace the source of the
> problem.
> >
> > After some research, I found a way to set up a custom trace that will
log
> > the output of the profiler to a log file (or can output to a table)....
> > please see xp_trace_addnewqueue in BOL.
> >
> > My concern is that the log file may get too big and may take up all the
> disk
> > space if it ran for too long. I also saw another extended procedure that
> > flushes the last 100 events from the trace to a log file.... but that is
> > only for a particular trace (please see xp_trace_setqueryhistory and
> > xp_trace_flushqueryhistory).
> >
> > My question therefore is, can I make the trace only dump the last... say
> > 5,000 events to the file when the deadlock occurs. I could setup an
Alert
> > for the  error number for the deadlock. This alert could then execute a
> > similar command as xp_trace_flushqueryhistory showing me only the last
> 5,000
> > events at the time the error occurs.
> >
> > Any help would be appreciated.
> >
> > Thank you.
> >
> >
>
>