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:
EDIT 1:
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.exe
again and now it worked fine;
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?
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 usedDTCPing
as you suggested and it showed an error that I corrected and the error with thedtctester.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!