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.
- The MSDTC Service is running on all SQL-Servers. None of the SQL-Servers has any Linked Servers.
remote trans procin sys.configurations is set to 0.
- I found no explicit
Begin DISTRIBUTED Transactionor
SET REMOTE_PROC_TRANSACTIONSusage in code
- no definition / usage of
- 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= "
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?