My SPID is sleeping but in a transaction holding resources!

Greetings Gurus.

Summary:
Q224453 doesn't help me resolve my sleeping SPID that's in a transaction and holding a resource, but with no wait type or wait time.

Details:
I come to this list after much investigation into a blocking problem I have been experiencing. After reading many of the posts here I'm optomistic of finding a lead to a resolution.

My investigations lead me to Q224453 (INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems) which was of great benefit, but did not help me fix my problem.

I am experiencing a Q224453 Scenario 2, or 'Blocking Caused by a Sleeping SPID That Has Lost Track of the Transaction Nesting Level'. In other words, I'm experiencing a spid in a transaction (trans_count>0) holding onto a resource, with no wait type (0x0000) or wait time(0). So it just sits there, SLEEPING, and majorly blocking other transactions.

When this block occurrs with no timeout values set, SQL Server does not resolve the block. No deadlock is found. (Possibly a Q239753 BUG: 1205 - Deadlock Situation Not Detected by SQL Server ? )

Q224453 says that in this case the cause is one of two things. Firstly, that the controlling app has lost track of the transaction after possibly, an unhandled error. I'm using COM+ to manage my transactions, and I have checked with caution the error handling in those components. (See platform summary below)

Secondly, a performance issue - a slow running query holding on to locks a little too long? When I look for slow performing queries using SQL trace, I find that the longest query takes 1140 ms. I'm pretty sure performance is not the issue. This said, I do notice that one of the SPID's within a blocked transaction (not the blocking one) may occasionally timeout (EventClass +Lock:Timeout). This occurs even when I have not set any timeout value on the connection, in the stored procedure or on the COM+ component.

Does anyone have any ideas where I should look next?

I'm curious to see if it's a Q239753 (BUG: 1205 - Deadlock Situation Not Detected by SQL Server) as I know that COM+ is using multiple SPIDs within a transaction boundary.

I have also noticed that occasionally I get an indication that the last wait type on the blocking SPID was a transaction control block (XCB). I dont know if this has implications or not.

Any help or suggestions would be greatly appreciated. Im happy to go and look again at anything.

Many thanks in advance,
James.

Quick Platform summary:
Windows 2000
VB6
Sql Server 7 sp4
COM+ handles transactions. Components call parametrized stored procedures.
mdac2.5sp1
One physical multi-processor server (2GB RAM) with transactions spanning two databases on the server, occasionally a transactable MSMQ takes part in a transaction.Hi, Sometimes a process will hang if the calling application is waiting for a response like a click to a dialog box. Perhaps the COM object has an unhandled error of some sore. If this is the case, the transaction is hanging waiting for an error confirmation from the caller.

Have fun,
LM:p|||Thanks LM.

I capture and log all errors that occur in COM+ so I'm pretty sure that that's not the issue though I'll triple check. I've even tried setting the components to run with unattended execution - meaning that all the errors would be written to a log and would not wait for user input.

The more peole I talk to about this the more I hear
"Gee, COM+ is clunky isn't it"
or
"We just moved all our transactions out of COM+ and into stored procedures".

Has anyone else had problem with transaction implemented in COM+? I've successfully written 2 apps which use COM+ with no problem, but this particular app requires much more server resources than the others...

Perhaps this note belongs on a number of lists?

Thanks all!