Menu
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?
265
On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start. Save this answer.
MSDTC is a separate Windows service which coordinates distributed transactions across SQL Server instances. When deploying SQL Server in a highly available environment like Windows Failover Clustering, there are certain best practices that can make the MSDTC service's behavior more predictable.
Right-click My Computer, and then click Manage. Expand the Services and Applications node, and then click the Services node. In the right pane, locate the Distributed Transaction Coordinator. Right-click Distributed Transaction Coordinator, and then click Properties.
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:
UPDATE: The Distributed Transaction Coordinator(MSDTC) and Transaction FAQ pulls together a great list of MSDTC resources.
133
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
