none
Transactional replication failing with connection was forcibly closed RRS feed

  • 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


    Friday, July 3, 2020 6:00 AM

All replies

  • 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);

    Friday, July 3, 2020 10:00 AM
  • 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.
    Friday, July 3, 2020 5:21 PM
  • 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.

    Monday, July 6, 2020 7:37 AM
  • 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.
    Monday, July 6, 2020 12:56 PM
    Moderator
  • Hi Udham Singh,

    Is the reply helpful?

    BR,

    MIAOYUXI

    Thursday, July 9, 2020 1:27 AM
  • 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

    Friday, July 24, 2020 9:24 AM