Hello,
I am having a huge replication issue in SQL Server, I am having mini dump files created from the distribution agents jobs related to Peer-to-Peer publications subscriptions (e.g. ReplAgent20201222123043_0.mdmp) . the third step of distribution agent job "Detect nonlogged agent shutdown" is failing with the error "String or binary data would be truncated". once this step failed, some replications commands are not sent and missed and never resent to the other node.
The dump file says:
C:\Program Files\Microsoft SQL Server\110\COM\rdistcom.dll from Microsoft Corporation has caused an access violation exception (0xC0000005) when trying to read from memory location 0x00000000. The error "the thread tried to read from or write to a virtual address for which it does not have the appropriate access".
Our Production environment consists of Windows Server 2012 R2 on a host environment with Windows Cluster, and SQL Server version is "Microsoft SQL Server 2012 (SP4-GDR) (KB4583465)" Enterprise Edition with SQL Server Failover Clustering (2 Nodes), the storage system is SAN.
Distribution job history:
Microsoft (R) SQL Server Replication Agent
A replication agent encountered a fatal error and was shut down. A mini-dump has been generated at the following location:
C:\Program Files\Microsoft SQL Server\110\Shared\ErrorDumps\ReplAgent20210601091008_0.mdmp
Date 01/06/2021 9:48:54 AM
Log Job History (SQLSERVER-SERVICEUNITS-PUB_SERVICEUNITS_PTP-SQL-REP-716)
Step ID 3
Server SQLSERVER
Job Name SQLSERVER-SERVICEUNITS-PUB_SERVICEUNITS_PTP-SQL-REP-716
Step Name Detect nonlogged agent shutdown.
Duration 00:00:00
Sql Severity 16
Sql Message ID 8152
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: PROXYMAIL\Replication. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152). The step failed.
My Replication environment was implemented five years ago and this bug hasn't occurred until few months ago.
I have searched and found blogs classing this error as a bug in
SQL Server code itself. many blogs suggest a workaround fix for the bug.
blogs:
https://sagittarius.agency/en/our-thinking/blog/fixing-sql-server-2014-replication
Experts I appreciate your valuable inputs.
BR,