I've been getting these two errors on SQL Server log:
DESCRIPTION: Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).
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.exeand here's the output:
Ran test using DTCping and it showed an error:
To fix DTCping error I Set the appropriate values for the EnableAuthEpResolution and RestrictRemoteClients options on the client side;
Tested the DTC with
dtctester.exeagain and now it worked fine;
- 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?