1

I've been getting these two errors on SQL Server log:

Error 1:

DESCRIPTION: Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).

Error 2:

DESCRIPTION: Enlist operation failed: 0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.

It happens at random times and I don't know how to track and associate it with any job or application request. No change has been made to the server that I'm awere of.

Here's what I have done so far:

  • Verified that Distributed Transaction Coordinator service is running on Windows;
  • Event Viewer didn't show any error I could relate to the problem;
  • Firewall is off while I'm troubleshooting;
  • Followed the instructions to Configure DTC Security;
  • Have seen similar threads like this and this;
  • Tried to uninstall and install MSDTC;
  • Tested the DTC with dtctester.exe and here's the output:
    output of dtctester

EDIT 1:

EDIT 2:

  • Configured Extended Events. It captured the error and brought information about the user, database and server involved on the error, but it wasn't enough to find out the culprit. Here's the code used to create the XE session:


CREATE EVENT SESSION [MSDTC_TROUBLESHOOT] ON SERVER 
ADD EVENT sqlserver.errorlog_written(
    ACTION(package0.callstack,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[client_hostname]=N'MyAppServer' AND [sqlserver].[database_name]=N'MyDatabase'))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Even though MSDTC passed the tests I'm still getting the two errors on SQL Server log. Is there anything else I can do find out what is causing them?

4
  • 1
    Are you testing a single host only, or between hosts? Also, have you tried DTCPing to verify basic communication for DTC is working? Link
    – HandyD
    Dec 18, 2019 at 22:43
  • @HandyD I'm testing between hosts. I haven't tried DTCPing. I thought I read an article which said there was no need to use it when the server being tested had a SQL Server. Probably my mistake after reading so many things. I used DTCPing as you suggested and it showed an error that I corrected and the error with the dtctester.exe changed. I was able to fix that other error too and now MSDTC seems to be working fine. I'm gonna write the steps in a complete answer. Thanks a lot!
    – Ronaldo
    Dec 19, 2019 at 20:08
  • @HandyD would Extended Events be a useful tool in this case? I've never used it. Any advise before I start digging with it?
    – Ronaldo
    Dec 21, 2019 at 12:25
  • Have you enabled the MSDTC log to try and identify the source? Also, try adding error_reported event into your XE session to capture details from the session that fails which might give you more information.
    – HandyD
    Jan 9, 2020 at 3:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.