setting up a trace through extended stored procedures

What about setting up a job, that resets the trace every
hour?

Now why didn't I think of that? Thanks!!

"Robert Lummert" <rl@w4u.com> wrote in message
news:3C7A0A6A.9010407@w4u.com...

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

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