Sql-server – transactional replication, failing with communication link failure half way through bcp

sql-server-2008sql-server-2012transactional-replication

I am trying to replicate to a 4th subscriber on a remote site. 3 subscribers work fine locally. the other difference is this is a 2012 subscriber from a 2008 r2 publisher and remote distributor.

When delivering the bcp commands it fails with the following error.

The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)

I have tried raising the historyverbose level to 2, but this is the only error i get from the agent history.. I have attempted to recreate the snapshot, but this still fails at the same point…

nothing unusual about the table, and its 5 million rows, so not massive amounts of data…

Column_name Type    Computed    Length
FlightId    int no  4
OrigAirport char    no  3
DestAirport char    no  3
DeptDate    datetime    no  8
ArriveDate  datetime    no  8
Airline char    no  10
SupplierId  int no  4
UpdateRef   int no  4
HarvestUpdateKey    int no  4
ActiveFlag  bit no  1

Can anyone help me diagnose this to get a useful error or suggest what the problem might be?? the connection is very slow to this datacentre which is a issue we are attempt to resolve… could this simply be a timeout ???

UPDATE – I configured a seperated publication with just this table in and set the verbose logging to 2. I then get

Error messages:
The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)

An existing connection was forcibly closed by the remote host.
Unspecified error
Failed to send batch after max errors
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)

Communication link failure (Source: MSSQLServer, Error number: 10054)

so looks like the network between datacentres… strange that it would always die at the same point, would there be a reason for this ?

The error log on the target server
Error: 4014, Severity: 20, State: 13
A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).

Best Answer

If the link is bad, you must overrride -QueryTimeOut and -KeepAliveMessageInterval parameters. Check the SQL Server Agent job, identify the replication Job, then right-click Properties, then Steps, Edit and at the end of Commend just add -LoginTimeOut 120 -QueryTimeOut 7200 -KeepAliveMessageInterval 600