Transactional replication failing with connection was forcibly closed

Question

Hi,

I have pull transactional replication setup. When there is a little load of transactions it starts failing with below error.

Agent message code 10054. TCP Provider: An existing connection was forcibly closed by the remote host.

All server (pub\sub and dist) are on SQL 2016 SP2.  Have other replication setup also between the servers but only one particular distributor agent is failing. Have verified the connection status between the serve when we encounter the error, network drivers are also up to date. No such auto close property is on.

What could be the possible reason and solution for the same?

Error messages:
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
Communication link failure (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
Communication link failure (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
Protocol error in TDS stream (Source: MSSQLServer, Error number: HY000)
Get help: http://help/HY000

And Some time:

Message
2020-06-30 02:52:51.043 Copyright (c) 2016 Microsoft Corporation
2020-06-30 02:52:51.043 Microsoft SQL Server Replication Agent: distrib
2020-06-30 02:52:51.043
2020-06-30 02:52:51.043 The timestamps prepended to the output lines are expressed in terms of UTC time.
2020-06-30 02:52:51.043 User-specified agent parameter values:
            -Publisher SERVERS1
            -PublisherDB XYZ_DBSchema
            -Publication XYZ_Reporting_REPL
            -Distributor SERVERS2
            -SubscriptionType 1
            -Subscriber SERVERS1
            -SubscriberSecurityMode 1
            -SubscriberDB XYZ_Reporting
            -Continuous
            -XJOBID 0xB5B1C0883E31AB4CBA4638D9DE82779A
            -XJOBNAME SERVERS1-XYZ_DBSche-XYZ_Reporting_-SERVERS1-XYZ_Reporting-0CF91B86-EBE3-4360-98FE-506C20656804
            -XSTEPID 1
            -XSUBSYSTEM Distribution
            -XSERVER SERVERS1
            -XCMDLINE 0
            -XCancelEventHandle 0000000000001DF4
            -XParentProcessHandle 0000000000001AAC
2020-06-30 02:52:51.043 Startup Delay: 4595 (msecs)
2020-06-30 02:52:55.652 Connecting to Subscriber 'SERVERS1'
2020-06-30 02:52:55.668 Connecting to Distributor 'SERVERS2'
2020-06-30 02:52:56.293 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 1
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors
            -transactionsperhistory 100
2020-06-30 02:52:56.605 Initializing
2020-06-30 02:53:04.199 101 transaction(s) with 3062 command(s) were delivered.
2020-06-30 02:53:04.652 100 transaction(s) with 194 command(s) were delivered.
2020-06-30 02:53:04.918 100 transaction(s) with 214 command(s) were delivered.
2020-06-30 02:53:13.168 Agent message code 10054. TCP Provider: An existing connection was forcibly closed by the remote host.
2020-06-30 02:53:13.308 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  10054
Message: TCP Provider: An existing connection was forcibly closed by the remote host.
2020-06-30 02:53:13.340 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  10054
Message: Communication link failure
2020-06-30 02:53:13.371 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  10054
Message: TCP Provider: An existing connection was forcibly closed by the remote host.
2020-06-30 02:53:13.402 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  10054
Message: Communication link failure
2020-06-30 02:53:13.434 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  10054
Message: TCP Provider: An existing connection was forcibly closed by the remote host.
2020-06-30 02:53:13.480 The agent failed with a 'Retry' status. Try to run the agent at a later time.

Thank you,

Udham Singh

Answer 1

I read your reply in this case: trans-repl-terminates-and-restarts-after-error

You say you do not have the network issue?And the error shows just when the table is big?

It may be becaused by other processes (services or applications) are running at the same time on the SQL Server server, making the CPU utilization of the entire server 100%, or using a lot of memory resources, which affects the response of the SQL Server service.

Run code as next to check:

DBCC OPENTRAN

Note:(network trouble shoot steps)

1.Close the firewall;

2.Make sure TCP\IP protocol is enabled, and port is1433;

3.If you have one domain account,use your same domain Administrator account to restart the services(SQL Engine,Agent,Browser) both in your Distributors' and Publisher's(Subscriber's);

Answer 2

UPU usage is between 30-40 percent. Able to perform other queries on subscriber server in timely manner. Does seems like memory pressure. Able to telnet the servers ports dirt to Subs and vice versa. Agent are running under domain service account having admin access on the server.

Answer 3

Excute code as next and then update here:

DBCC MEMORYSTATUS

Or if you are sure that is one memory issue, choose to extent the memory or excute the job in batch.

Answer 4

This is a port issue. You need to open possibly port 1433 for inbound communication on your subscriber, or make sure your sql server is running on the subscriber.

Answer 5

Hi Udham Singh,

Is the reply helpful?

BR,

MIAOYUXI

Answer 6

Hi,

I'm following this open case and is the issue solved?

You can choose to mark the helpful reply as answer  to close this case.

BR,

MIAOYUXI