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.



Mon, 25 Feb 2002 02:30:03 GMT
"Adam" <nospam@nospam.com>
setting up a trace through extended stored procedures
What about setting up a job, that resets the trace every
hour?


Mon, 25 Feb 2002 10:56:58 +0100
Robert Lummert <rl@w4u.com>
Re: setting up a trace through extended stored procedures
Now why didn't I think of that? Thanks!!


"Robert Lummert" <rl@w4u.com> wrote in message
news:3C7A0A6A.9010407@w4u.com...
> What about setting up a job, that resets the trace every
> hour?
>



Tue, 26 Feb 2002 12:45:07 GMT
"Adam" <nospam@nospam.com>
Re: setting up a trace through extended stored procedures
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.
>
>



Tue, 26 Feb 2002 08:17:53 -0500
"Brian Moran" <brianm@crosstier.com>
Re: setting up a trace through extended stored procedures
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.
> >
> >
>
>



Wed, 27 Feb 2002 04:16:29 GMT
"Adam" <nospam@nospam.com>
Re: setting up a trace through extended stored procedures