Posted by John Bell on 01/14/06 19:34

Hi Don

It could be that you will never be able to replicate the deadlock if your
hardware/environment is exactly the same. If you have not run
sp_blocker_pss80 you may want to try it
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509

John

"Don Vaillancourt" <donv@webimpact.com> wrote in message
news:bWVxf.9204$43.7861@nnrp.ca.mci.com!nnrp1.uunet.ca...
> Oh, I know which queries are involved and which ones are usually the
> victims.
>
> But thanks for the trace idea.
>
> We haven't been able to replicate the deadlock issue in-house ass of yet,
> but I will certainly keep those options in mind and use them.
>
> Thank you
>
>
> Erland Sommarskog wrote:
>> Don Vaillancourt (donv@webimpact.com) writes:
>>> We have a problem with a table giving us deadlock issues and we can't
>>> figure out why.
>>>
>>> It's a table we write to fairly often perhaps 50 times a minute. And
>>> also do a select of 200 rows at a time from 4 servers every 5 minutes or
>>> so.
>>> We are only keeping 48 hours worth of rows in the table which averages
>>> at 30000 a day on a busy day.
>>>
>>> This table has 1 PK and 2 FKs plus one TEXT column which does not
>>> participate in the WHERE clause.
>>>
>>> We are using binded variables.
>>>
>>> We have applied the latest patch to SQL2003 server running on
>>> Windows2003. The patch is supposed to resolve deadlock issues.
>>>
>>> Anyone have any advice on how to alleviate this problem.
>>
>> I'm afraid that there is not enough information your post to make it
>> possible to give solutions.
>>
>> Except one: if it is acceptable that one of the process is always
>> is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
>> We have done this in quite a few places in our system. Background
>> processes don't scream so much about deadlocks as users do.
>>
>> But if that is not an option, I can only suggest methods to get more
>> information.
>>
>> First, have you enabled deadlock trace on your server and looked at
>> the output? To enable deadlock trace, use Enterprise Manager to add
>> these two startup options: -T 1204 -T 3605.
>>
>> Once you have the deadlock output, try to narrow down exactly which
>> queries that collide. Once you have the queries, you could post them
>> together with the table definitions (including indexes!). Or you could
>> post the deadlock traces (which is not very easy to interpret).
>>

[Back to original message]