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 INSERT.

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.

  • 1
    What is the Query Timeout and Connection Timeout set to on your Linked Server object? Any chance the machine of the Linked Server changed in any way recently (specifically that would affect the Microsoft Distributed Transaction Coordinator settings)?
    – J.D.
    Jun 23 at 13:54
  • it's both set to 0. I noticed that the job normally runs for (on this step ) 40 minutes, and now its taking more than 1hr. yes probbly somewhere is hitting a timeout limit. but nothing was changed we are sure of it.
    – Racer SQL
    Jun 23 at 14:12


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.