How Can I Find What's Causing My Transaction to Get Promoted?

Question

I have web site which serves web services (a mixture of .asmx and WCF) which is mostly using LINQ to SQL and System.Transactions. Occaisionally we see the transaction get promoted to a distributed transaction which causes problems because our web servers are isolated from our databases in such a way that it is not possible for us to use MSDTC.

I have configured 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>

It's very interesting and shows me when the transaction is promoted, but I find that it doesn't really help be discover why.

Is there an equivalent tracing mechanism for ADO.NET that will show me when connections are created, including the variables that affect pooling (user, cnn string, transaction scope)?

Answer 1

You could take a look at Data Access Tracing in SQL Server 2005 (there is also a link to the SQL Server 2008 version there). That article describes how to configure tracing and how to read the generated files. I'm not sure if it will give you exactly what you want but it will definitely show the connections/connection strings. On the downside, there is a lot of output generated.

Another option would be to run SQL Server Profiler or server-side trace to capture connections, SQL statements and transactions.