Since last week, one of our jobs started failing with this error:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction
It's a stored procedure. It runs for more than 1hr then it aborts and displays the error above. It mostly tries to execute an
I noticed that when the query runs we have a
CXCONSUMER wait stats.
Also I noticed that, when I ran it manually, I could see on
sp_whoisactive that the session I was using has
SID=50 for example, and on event viewer I see:
Session 50 was killed by hostname
Nothing was changed and I'm failing to understand why. I saw some posts from Brent Ozar mentioning about setting
MAXDOP =1 and I'm going to test it, but for this problem, it's not the answer to what that error is.