How can I find the reason why my transaction will be promoted?
I have a website that serves web services (a mixture of .asmx and WCF) that mainly uses LINQ to SQL and System.Transactions. We sometimes see a transaction propagate to a distributed transaction, which causes problems because our web servers are isolated from our databases in such a way that we cannot use MSDTC.
I set up tracing for System.Transactions by adding the following to my web.config:
<system.diagnostics>
<sources>
<source name="System.Transactions" switchValue="Information">
<listeners>
<add
name="tx"
type="System.Diagnostics.XmlWriterTraceListener"
initializeData="tx.log"
/>
</listeners>
</source>
</sources>
</system.diagnostics>
This is very interesting and shows me when a transaction is progressing, but I find it doesn't really help to find out why.
Is there an equivalent tracing mechanism for ADO.NET that will show me when connections will be created, including variables affecting the join (user, cnn string, transaction scope)?
a source to share
You can take a look at Tracing Data Access in SQL Server 2005 (there is also a link to the SQL Server 2008 version). This article describes how to set up tracing and how to read the generated files. I'm not sure if this will give you exactly what you want, but it will definitely show connections / connection strings. On the other hand, there is a lot of output.
Another option would be to run SQL Server Profiler or server-side tracing to capture connections, SQL statements, and transactions.
a source to share