views:

310

answers:

1

How can I reliably check that MSDTC has promoted a transaction to a distributed transaction?

This is when using TransactionScope in .net.

Currently a co-worker is testing this by shutting down the coordinator on his machine - if an exception is thrown this is taken as evidence that an attempt to promote the transaction has occurred. Is this a valid test?

+5  A: 

I think your test is OK although you should ensure that you are getting a DTC exception and not some other exception.

Some other things you could do:

  • You could also run SQL Profiler and under Transactions trace DTCTransaction.

  • In terms of code, you could handle the DistributedTransactionStarted event and log a message when a distributed transaction is started.

  • Or you could just add log messages to log the System.Transactions.Transaction.Current. TransactionInformation.DistributedIdentifier before the end of the transaction. If the value is Guid.Empty {00000000-0000-0000-0000-000000000000} then it is not a distributed transaction otherwise the transaction has been promoted to a distributed transaction.

You said you were using SQL Server 2008. What version of .NET are you using? Is it 3.5? If you are using SQL Server 2008 and .NET 3.5 then you should be able to open multiple connections (using the same connection string) to the same database in the same transaction without escalating to a distributed transaction. For this to work you need to close the first connection before opening a second connection.

If it appears that all the conditions are met and the transactions are still escalating, I would:

  • double check the SQL Server compatibility level
  • check the connection string to see if pooling is disabled
  • check to see that two connections are not opened at the same time in one transaction
  • find out if all transactions are being promoted or only in certain scenarios

UPDATE: The Distributed Transaction Coordinator(MSDTC) and Transaction FAQ pulls together a great list of MSDTC resources.

Tuzo
Thanks for the detailed answer - where did you acquire such knowledge? Can you recommend any resources on MSTDC?
Oded
MSDTC is great when it is working. When it's not working is when you learn the most about it. :) In general, MSDN has the best MSDTC resources. For troubleshooting http://support.microsoft.com/kb/306843 has usually been the goto article but this one, http://msdn.microsoft.com/en-us/library/aa561924%28BTS.10%29.aspx , is very good as well.
Tuzo
+1 for the detailed answer
IgorK