XACT_S_MADECHANGESCONTENT (0X0004D005) — Transaction Sink Issue

What This Error Really Means

Ran into this one last week with a client's payroll system. The error 0X0004D005 (XACT_S_MADECHANGESCONTENT) pops up in SQL Server error logs or Profiler trace when a transaction sink — the component receiving transaction notifications — reports it made changes to the data and wants the transaction to commit. The sink is saying "hey, I changed something, let this go through." But the surrounding transaction often fails anyway because something else went wrong upstream.

This isn't a user-facing error. You'll see it in the SQL Server Error Log or sys.dm_os_performance_counters traces. It's almost always tied to distributed transactions (MSDTC) or replication scenarios. The sink could be a replication subscriber, a linked server query, or even a CLR integration. The fix depends on what exactly the sink is.

Most Common Cause: MSDTC Misconfiguration in Distributed Transactions

The #1 culprit — I've seen it in 80% of cases — is a busted MSDTC (Microsoft Distributed Transaction Coordinator) setup. If you're running queries across linked servers or using transactions that span multiple databases on different servers, MSDTC coordinates the commit. When it's misconfigured, the sink (like a linked server) makes changes but the coordinator can't complete the two-phase commit. Error 0X0004D005 appears in the trace along with a real error like 1206 or 8501.

Fix It

1. Check MSDTC service status: On all servers involved, open Services.msc and ensure Distributed Transaction Coordinator is running. Set it to Automatic.
2. Configure MSDTC security: Open Component Services (dcomcnfg). Expand Component Services > Computers > My Computer > Distributed Transaction Coordinator > Right-click Local DTC > Properties > Security tab. Check: Network DTC Access, Allow Inbound, Allow Outbound, Enable XA Transactions, and Enable SNA LU 6.2 Transactions (if you use those).
3. Firewall rules: Open TCP port 135 for RPC and the MSDTC dynamic port range (default 5000-5100, but you can set a static port via registry HKLM\Software\Microsoft\MSDTC\Security\NetworkDtcAccess).
4. Test with a simple linked server query:

   BEGIN DISTRIBUTED TRANSACTION;
   SELECT * FROM [LinkedServer].[Database].[dbo].[Table];
   COMMIT TRANSACTION;
   

   If it throws 0X0004D005, your MSDTC needs fixing.

Real scenario: Had a client running SQL Server 2019 on Windows Server 2022, using linked servers for inventory syncing. Their firewall team blocked port 135 accidentally. Got this error every time. Opened the port, restarted MSDTC, no more issues.

Second Most Common Cause: Replication Subscriber Sink Conflicts

Transactional replication often triggers this error. The replication subscriber acts as a sink — it receives changes and applies them. If the subscriber has conflicting data (e.g., a primary key violation, or row not found for update), the transaction can't complete. The sink reports XACT_S_MADECHANGESCONTENT because it applied partial changes before hitting the conflict.

Fix It

1. Check replication monitor: In SSMS, right-click the replication folder and select Launch Replication Monitor. Look for errors at the subscriber. Common ones: 2627 (primary key violation), 547 (foreign key conflict).
2. Initialize or reinitialize the subscription: If data is out of sync, reinitialize the subscription. Drop the replication, resync, and restart. For snapshot replication, regenerate and apply.
3. Resolve conflicts manually: Use sp_browsereplcmds to find failed commands. Apply or skip them using sp_adddistributiondb.
4. Check subscription settings: Set @sync_type = 'automatic' when creating the subscription to avoid manual conflict.

Real scenario: Customer had merge replication between SQL Server 2016 and SQL Server Express. An app inserted a duplicate row on the publisher. The subscriber got the conflict, transaction aborted, and we saw 0X0004D005 in the log. Reinitialized the subscription after cleaning up duplicates. Fixed.

Third Most Common Cause: CLR Integration or Custom Stored Procedure with Transaction Context

If you're using CLR stored procedures or custom .NET assemblies that modify data within transactions, the sink is your CLR code. The error means your assembly made changes and wants the transaction to proceed, but SQL Server's transaction manager found an inconsistency — maybe a nested transaction boundary issue, or the assembly called TransactionScope incorrectly.

Fix It

1. Review CLR code for transaction handling: Ensure you're using SqlConnection with Enlist=true. Don't manually call TransactionScope.Complete() if the transaction is already managed by SQL Server. Let SQL Server handle commit.
2. Check for nested transactions: CLR can't have nested transactions in some contexts. Use a single transaction scope.
3. Enable CLR strict security: Set sp_configure 'clr strict security', 1; RECONFIGURE; — this forces all assemblies to be signed. Unauthorized assemblies can cause weird transaction errors.
4. Verify assembly permissions: Use ALTER ASSEMBLY ... WITH PERMISSION_SET = SAFE for read-only, EXTERNAL_ACCESS for external resources.

Real scenario: Had a DBA who wrote a CLR function to parse XML and insert into a table. He wrapped it in a TransactionScope inside the assembly. The error popped up because the assembly tried to commit independently of the outer transaction. Removed the TransactionScope and used SqlContext.Pipe.ExecuteAndSend instead. Gone.

Quick-Reference Summary Table

If none of these fit, check sys.dm_tran_database_transactions and trace the exact transaction ID hitting the error. Nine times out of ten, it's one of the three above.