Re: Trace Flag 1204

From: Steve Cockayne (steve_at_nospam.com)
Date: 06/26/04 

Date: Fri, 25 Jun 2004 17:27:37 -0700

OK Greg - so, more specifically, I have a test system that, every now and
then, pops out a message that says "there was a deadlock, re-submitting
query." It names one of the culprit stored procedures, but not the other
one(s).

However, it doesn't give the entire output that trace flag 1204 would give,
so I am faced with the prospect of running a test server with trace flag
1204, likely for some time, until this deadlock occurs, in order to find out
what is going on. So, not production, but I would be running it 24 by 7 by
however long it takes to end up with the full deadlock trace.

I am wondering specifically if the performance of the SQL Server will
degrade while I am running it with trace flag 1204, or if whatever heavy
lifting trace flag 1204 needs to do is only incurred actually when a
deadlock occurs, as I am hoping to piggyback my deadlock capture onto a long
term system load test, where we might capture performance statistics that,
in turn, might be degraded by the use of trace flag 1204.

What I was looking for was someone with knowledge of how the support for
Trace flag 1204 is coded up inside the SQL Server engine, who would know for
sure.

Cheers,

Steve.