Sample Header Ad - 728x90

Why are transactions running as distributed (DTCXact)?

4 votes
2 answers
2712 views
I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query against production servers: SELECT DTAT.transaction_id , DTAT.[name] , DTAT.transaction_begin_time , CASE DTAT.transaction_type WHEN 1 THEN 'Read/write' WHEN 2 THEN 'Read-only' WHEN 3 THEN 'System' WHEN 4 THEN 'Distributed' END AS transaction_type , CASE DTAT.transaction_state WHEN 0 THEN 'Not fully initialized' WHEN 1 THEN 'Initialized, not started' WHEN 2 THEN 'Active' WHEN 3 THEN 'Ended' -- only applies to read-only transactions WHEN 4 THEN 'Commit initiated'-- distributed transactions only WHEN 5 THEN 'Prepared, awaiting resolution' WHEN 6 THEN 'Committed' WHEN 7 THEN 'Rolling back' WHEN 8 THEN 'Rolled back' END AS transaction_state , CASE DTAT.dtc_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Prepared' WHEN 3 THEN 'Committed' WHEN 4 THEN 'Aborted' WHEN 5 THEN 'Recovered' END AS dtc_state FROM sys.dm_tran_active_transactions DTAT INNER JOIN sys.dm_tran_session_transactions DTST ON DTAT.transaction_id = DTST.transaction_id WHERE [DTST].[is_user_transaction] = 1 ORDER BY DTAT.transaction_begin_time It always shows results similar to this sample: > 2414848764;DTCXact;2016-01-28 > 10:24:41.983;Distributed;Committed;Committed > 2414896908;DTCXact;2016-01-28 > 10:26:05.847;Distributed;Committed;Committed > 2414903917;DTCXact;2016-01-28 > 10:26:29.017;Distributed;Committed;Committed > 2414918503;user_transaction;2016-01-28 > 10:27:06.823;Read/write;Active;NULL 2414918551;DTCXact;2016-01-28 > 10:27:06.973;Distributed;Committed;Committed This result shows that most transactions run as distributed. I really don't know why at all. Some of the servers are clustered with two nodes. Others are not. Their results are similar. **Facts**: - The MSDTC Service is running on all SQL-Servers. None of the SQL-Servers has any Linked Servers. - remote trans proc in sys.configurations is set to 0. - I found no explicit Begin DISTRIBUTED Transaction or SET REMOTE_PROC_TRANSACTIONS usage in code - no definition / usage of TransactionScope in Code - Found a class described as follows: //COM+ Transaction mgr using "Service without Components" model built according to System.Transactions.TransactionScope (NET 2). I don't know what this means. The asp.net web application causing those transactions usually always reads and writes data from one and the same database on one and the same SQL-Server. There MAYBE some very few exceptions, especially running during nighttime copying some data around, but I would not expect to see the MAJORITY of transactions being distributed the whole day long. I traced the actios of a SPID that I found was running distributed transactions: Here is a sample result of tracing: I see dozends of RPC:Completed Events in a row followed by 2 DTCTransaction Events (1st: EventSubClass="Enlisting in a DTC transaction", 2nd EventSubclass= "Propagate Transaction"). All target the same DB. No TM:Promote Tran xxx Events happen. I found out that those queries are running on the IIS (where the Web Application is hosted) in a .net Windows Service, that uses the same database as well and executes jobs. May this 2nd application cause DTC?? I searched around the web without finding similar questions or any answers. So I wonder what could be the reason for this? Or what knowledge do I miss to understand this?
Asked by Magier (4827 rep)
Jan 28, 2016, 09:39 AM
Last activity: Nov 1, 2018, 08:34 PM