question

VaheBoyadjian-7596 avatar image
0 Votes"
VaheBoyadjian-7596 asked Cathyji-msft answered

Peer to peer replication Memory allocation errors and mini dump file creation for "access violation exception"

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://dba.stackexchange.com/questions/274573/replication-error-masked-by-sp-msdetect-nonlogged-shutdown-returning-string-or

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fa887b71-dc5a-49d8-b01f-823b64b3768e/trying-to-report-bug-in-sql-server-replication-with-spmsdetectnonloggedshutdown-stored-procedure?forum=sqlreplication

https://sagittarius.agency/en/our-thinking/blog/fixing-sql-server-2014-replication


Experts I appreciate your valuable inputs.



BR,

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Those kinds of errors are almost always related to software bugs. However, SQL 2012 is "extended support" and you can contact MS Support and see if they will help you, but they will likely tell you to upgrade to a supported version.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @VaheBoyadjian-7596,

This is a bug in MS SQL Server 2012 or SQL server 2014. Refer to the MS blog KB3081580 - FIX: "String or binary data would be truncated" error when you use peer-to-peer replication in SQL Server 2012 or SQL Server 2014.

But you have applied the latest patch for SQL server 2012. And the issue generated a dump file in your environment, suggest you to ask professional engineer for help, and they will deal with your problem separately and confidentially.https://support.microsoft.com/en-us/assistedsupportproducts


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.