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
orSET 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?